Using column with numbers and leading zeros

[Jake]

Hi,

I have a column with social security number where leding zeros occur
often. Excle strips them off, but I have gotten around this by defining
a custom format of '00000000000', which works fine.

However I also need to pick out certain digits from this number using
Left() and Mid(), and these functions does not take the 'artificially'
added zeading zero into account...

How can I in an excel cell convert the numeric values to strings with
leading zeros and that these leftpadded strings can be currectly
manipulated with Left() and Mid()

Thanks for a solution to this

geir

 

[Patrick Molloy]

you can set the cilumns format to be TEXT. which would preserve th eleading
zeros. Otherwise Excel sees the soc sec codes as regular numbers and hence
strips away the leading zeros

you could use =TEXT(LEFT("00000000000",12 -LEN(A1) ) & A1,"#")

 

[Luke M]

You could either change the format of the cell you are entering the data into
to text, and manually input the leading zeroes, or, use the TEXT function in
combination with Left() and Mid() such as:

=LEFT(TEXT(A2,"00000000000"),4)