Leading Zeros in Numeric Values

G

Guest

I need to combine or concatenate, two, three-digit values into one, six-digit
value. For example, in column A, I have the value 123 and in column B, I
have the value 456. In column C, I concatenate A & B into the text 123456.
I run into a problem when the three digit values in columns A and B contain
leading zeros, such as 031, 002, 067, etc. How do I combine or concatenate
the two, three-digit values into one, six-digit value when one or both of the
three-digit values contain leading zeros?

J

JE McGimpsey

One way:

C1: =TEXT(A1,"000") & TEXT(B1,"000")

J

Jason Morin

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

HTH
Jason
Atlanta, GA

G

Guest

As long as both values that you are concatenating are stored as text values
they should concatenate fine.

Check the Cell Format and make sure you have selected text.

G

Guest

Thank you, Jason! Your formula was what I needed.

Dave

G

Guest

Thank you, JE! Your formula is what I was looking for.

Dave

G

Guest

The values are stored as numeric values, not text. they are formatted with
the custom formatting 000. Converting a numberic value that contains one or
more leading zeros to text by using the format cells, text command will
remove the leading zeros from the numeric value. Thanks for your response,
the formula provided by Jason and JE is what I was looking for.

Dave

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.