Leading Zeros in Numeric Values
 

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?

One way:

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

In article ,
DBavirsha wrote:

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?

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

HTH
Jason
Atlanta, GA

-----Original Message-----
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?
.

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.

"DBavirsha" wrote:

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?

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

Dave

"Jason Morin" wrote:

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

HTH
Jason
Atlanta, GA

-----Original Message-----
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?
.

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

Dave

"JE McGimpsey" wrote:

One way:

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

In article ,
DBavirsha wrote:

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?

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

"Edgar Thoemmes" wrote:

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.

"DBavirsha" wrote:

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?