How do I get zeros to stay in front of a # when using the concate.

[czarkzm]

Hello all!

I'm trying to concatenate an invoice # with another field, and the problem
I'm having is that Excel drops the 0's in front of numbers. I can get them
to re-appear using custom formating, but when I use the concatenate or lookup
functions, the 0's aren't really there so my opperation fails!?!

HELP ME!!!

Thanks in advance for your thoughts.

Monk

 

[Fredrik Wahlgren]

Hello all!

I'm trying to concatenate an invoice # with another field, and the problem
I'm having is that Excel drops the 0's in front of numbers. I can get them
to re-appear using custom formating, but when I use the concatenate or lookup
functions, the 0's aren't really there so my opperation fails!?!

HELP ME!!!

Thanks in advance for your thoughts.

Monk

Let the first character be an apostrophe like this: '007

/Fredrik

 

[Jason Morin]

See:

http://j-walk.com/ss/excel/usertips/tip037.htm

Try using a format of =TEXT(A1,"00000000").

HTH
Jason
Atlanta, GA

 

[Peo Sjoblom]

You can use the text function

=TEXT(A1,"000")&B1

change the number of zeros to what you need or if you always want to pad
with one leading zero

=TEXT(A1,REPT("0",LEN(A1)+1))&B1

 

[czarkzm]

Thanks! That's exactly what I was looking for.

Monk

PS. Great site! Definitely going to bookmark that one!

 

[czarkzm]

Thanks Fredrik, but I have 30,000+ lines - looking for a formula since I'm
lazy!