PDA

View Full Version : Solved: Dealing with numbers with leading 0's



OdiN
08-13-2007, 09:04 AM
I have a spreadsheet of account numbers.

There are 3 parts to an account number - each in it's own cell.

I need to make this into a single account number.

I'm using Concatenate(A1,A2,A3)

However, there are some accounts which have leading zeros. I tried applying a custom format to get the zeros to show, but they won't move over when I concatenate.

For example if I have the following account:

05 555 00555

When I concatenate I get 5555555 and what I need is 0555500555.

Is there an easy way to handle this? I was able to do a search/replace on a lot of them because they first numbers are standard. The others there are tons of values so I can't do that.

Bob Phillips
08-13-2007, 09:31 AM
=TEXT(A1,"00")&TEXT(A2,"000")&TEXT(A3,"00000")

Bob Phillips
08-13-2007, 09:33 AM
Another way

=TEXT(A1*100000000+A2*100000+A3,"0000000000")

Bob Phillips
08-13-2007, 09:34 AM
... or ... to make it more readable

=TEXT(A1*10^8+A2*10^5+A3,"0000000000")

OdiN
08-13-2007, 09:37 AM
Ahhh...thanks :)