add zeros to front of text string

  • Thread starter Thread starter Napkintoe
  • Start date Start date
N

Napkintoe

I am importing data that always has 10 places. (ISBN numbers)

Some of the ISBN's start with either 1 or 2 zero's I want to tell excel
to always make the srting 10 places long by adding zero's if
necessary.

28404203 should be 0028404203

658009729 should be 0658009729

3765034291 should be left as it is.

I thought the =text function was the answer but I am stymied.

Thanks for your help
 
Here ya go

=+CHOOSE(10-LEN(D7),"0","00","000","0000","00000","000000")&D7

What this does is decides how many 0s to put in front of the number based on
how long it is, then joins it with the original "number" (because it's
text).
 
actually the number doesn't have to be text, but the result will be text. as
a side note, presumably the ISBNs are stored as strings of length 10 in the
file you are importing from, right? If so you should be able to import them
in that format instead of importing as numbers, then converting to text
strings as the formula will do.

also in case you didn't notice, that formula will work if the value is as
small as 4 digits. if you want to go smalller just continue the pattern.


Dave R. said:
Here ya go

=+CHOOSE(10-LEN(D7),"0","00","000","0000","00000","000000")&D7

What this does is decides how many 0s to put in front of the number based on
how long it is, then joins it with the original "number" (because it's
text).
Click to expand...
 
Thanks for the great suggestions!

Here is what I ended up using and it works so far

Highlight Column
Format cells: Number :Custom
Choose the "0"
Enter 10 Zeros and Wallah!

I hope I'm not missing something but this seems to be the easiest way
to handle this.

Thanks again
 
Highlight Column
Format cells: Number :Custom
Choose the "0"
Enter 10 Zeros and Wallah!
Click to expand...

Sure that'll *look* right, however it doesn't actually change the string
length, which is what I thought you wanted. But hey ...

Best rgds,
Andy
 
Back
Top