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.

Thanks in advance for your help,
Ellen
 
J

Jacob Skaria

Try and feedback

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

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


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

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


Or:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mix text and numbers with leading zeros 3
Keep First Letters, with exceptions 0
function result losing leading zeros 3
Keeping Leading Zeros; How Please ? 2
Leading Zeros; How to Keep ? 2
Fomat cells for IP address including leading Zeros 6
Add leading zeros and concatenation 4
Add leading zeros 5

Top