alphanumeric formating

R

Robin Krupp

This question is relating to book ISBN's. I want to find a way to create a
formatting so that when i enter "31062356x" , the excel will format it like
"03106256x"

"234" = "0000000234"
"1256b" = 000001256B"

basically a ten digit format tthat allows for a alpha char at the end

Thanks for any input
 
C

CLR

One crude way would be to enter your numbers (without leading zeros) in
column A, and your suffix letter in column B........then in column C put this
formula and copy down...

=TEXT(A1,"0000000000")&B1

Then do Copy > PasteSpecial > Values on column C and delete columns A and B

Vaya con Dios,
Chuck, CABGx3
 
S

Sloth

Excel doesn't really allow for the mixing of text and numbers, as far as
formatting goes. A cell contains either a number or a text string. Also,
the formatting for text is extremely limited, when compared to numbers. I
believe, your only options are to use VBA or use a formula in a different
cell like this...

=IF(ISNUMBER(--RIGHT(A1,1)),TEXT(A1,"0000000000"),TEXT(--LEFT(A1,LEN(A1)-1),"000000000")&RIGHT(A1,1))

NOTE: this formula gives an error if the cell contains no numbers, or any
letters other than one at the end.
 
Top