Keep Leading Zeros in Concatenation

  • Thread starter Thread starter EllenM
  • Start date Start date
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.

Thanks in advance for your help,
Ellen
 
Try and feedback

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

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


If this post helps click Yes
 
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.

Thanks in advance for your help,
Ellen
Click to expand...


Or:

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