# Keep Leading Zeros in Concatenation

E

#### EllenM

Hello,
I have a column of numbers that I've added leading zeros to make 6 digits.
I've done this using the a custom format of "000000". So 1 becomes 000001,
or 101 becomes 000101.

I've like to concatenate it with "Agency Response Letter No.", the result
beiing "Agency Response Letter No. 000001" or "Agency Response Letter No.
000101".

Excel, however, is dropping the leading zeros so that I get "Agency Response
Letter No. 1" or "Agency Response Letter No. 101".

How can I keep the leading zeros in the concatentation.

Ellen

G

#### Gary''s Student

="Agency Response Letter No." & TEXT(A1,"000000")

J

#### Jacob Skaria

Try and feedback

A1 = number
B1 = "Agency Response Letter No."

= B1 & " " & TEXT(A1,"000000")

If this post helps click Yes

T

#### Teethless mama

Another way...

="Agency Response Letter No. "&REPT(0,6-LEN(A1))&A1

G

#### Glenn

EllenM said:
Hello,
I have a column of numbers that I've added leading zeros to make 6 digits.
I've done this using the a custom format of "000000". So 1 becomes 000001,
or 101 becomes 000101.

I've like to concatenate it with "Agency Response Letter No.", the result
beiing "Agency Response Letter No. 000001" or "Agency Response Letter No.
000101".

Excel, however, is dropping the leading zeros so that I get "Agency Response
Letter No. 1" or "Agency Response Letter No. 101".

How can I keep the leading zeros in the concatentation.

Ellen

Or:

="Agency Response Letter No. "&RIGHT("000000"&A1,6)

E

#### EllenM

Thanks to everyone who responded.

Ellen