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 <[email protected]>,
DBavirsha <[email protected]> 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 <[email protected]>,
> DBavirsha <[email protected]> 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?