Format Alphanumeric Cells as Numbers

E

emptysky1969

If i have a series of cells that are alphanumeric (i.e. 43A), is there a
way to format that as a number so that it will show up as '43A' in a
graph?
 
H

HS Hartkamp

I think I do not understand in what way you would like to plot '43A'.

If you want it to be a label, located along some scale on position 43, then
you'd need to split the label into '43' and 'A'. To do this, put an extra
column in, and use formulas:
A1 containing "43A"
B1 containing =VAL(LEFT(A1,2))
and use column B as input for the graph. Then use column A as chart-labels
(see Rob Bovey's chart labeler elsewhere or use Google)

More complex:
If the number of positions in the numerical part vary (e.g. from 1 to
10000), you can precede the label with zeroes, and use:
B1 containing =VAL(LEFT(RIGHT("0000000"&A1,6),5))

Even more complex:
If the number of alphanumerical positions vary use some complex if / mid
combinantion to determine where the boundary is between the numerical and
the alphanumerica part is. Infinite possibilities.

Does this help ?

Bas Hartkamp.



"emptysky1969" <[email protected]>
schreef in bericht
news:[email protected]...
 
Top