Solved: strings add a zero

[khalid79m]

I need a formula to look at a cell say A2 AND if there is only 7 characters then place a 0 infront of it.

eg a2 = 9988778 then it should be 09988778

any help , i need a quick turn around as its holding all my work up :(

[RichardSchollar]

Hi

To do exactly what you asked you could use something like:

=IF(LEN(A2)=7,TEXT(A2,"00000000"),A2)

However, I think you should consider what you want to happen in this formula if their are fewer than 7 characters or more than 8 characters, and does this apply to only numeric terms in A2?

Richard

Richard

[khalid79m]

this is a staff number , so its numeric only, all staff numbers must be 8 digits long and the only time it will be 7 digits long is if the first digit is a 0 ... so is the formula you mention good to work with?

[RichardSchollar]

Yes, assuming that the value within A2 is held as a numeric value (it sounds as it it will be, otherwise the leading zero probably wouldn't be dropped). Given that the result of the formula isn't a numeric value if it is only 7 digits long (it becomes a text string containing numeric characters if that makes sense) then I suspect you actually want the formula to be:

=TEXT(A2,"00000000")

which means that all data returned by this formula will be textual strings of numeric characters.

I take it you need to use this in some form of a lookup? The alternative is that it is purely for display purposes - in which case you could probably simply format the cells containing these staff numbers (using Format>Cells>Number Format tab: Custom and then type in 00000000) in which case 7 digit staff numbers will be displayed with the leading zero).

Richard

[khalid79m]

thanks , great work