I am trying to concantenate three columns of data that contain numbers.

The first column has 2 numbers, the second has 4, and the third has 2. When
I combine the three nubmers they form a budget group, so all digits are
necessary.

In the second and third column, many have lead zeroes, or even have all zero
digits. When using the Concatenate or & function, it eliminates the leading
zeroes or leaves out a column if it is all zeroes.

Is there a way around this?

Ex: Column 1: 14 Column2: 0098 Column 3: 15
Currently: =A1&B1&C1 gives 149815 versus the needed 14009815

Any help is appreciated. Thank you!

Do you need to do any mathematical calculations with the numbers? You could
format all the numbers as text and then use the same forumla as you used
before, it should then show the zeros.

"Ash" wrote:

I am trying to concantenate three columns of data that contain numbers.

The first column has 2 numbers, the second has 4, and the third has 2. When
I combine the three nubmers they form a budget group, so all digits are
necessary.

In the second and third column, many have lead zeroes, or even have all zero
digits. When using the Concatenate or & function, it eliminates the leading
zeroes or leaves out a column if it is all zeroes.

Is there a way around this?

Ex: Column 1: 14 Column2: 0098 Column 3: 15
Currently: =A1&B1&C1 gives 149815 versus the needed 14009815

Any help is appreciated. Thank you!

On Fri, 23 Feb 2007 08:42:10 -0800, Ash wrote:

I am trying to concantenate three columns of data that contain numbers.

The first column has 2 numbers, the second has 4, and the third has 2. When
I combine the three nubmers they form a budget group, so all digits are
necessary.

In the second and third column, many have lead zeroes, or even have all zero
digits. When using the Concatenate or & function, it eliminates the leading
zeroes or leaves out a column if it is all zeroes.

Is there a way around this?

Ex: Column 1: 14 Column2: 0098 Column 3: 15
Currently: =A1&B1&C1 gives 149815 versus the needed 14009815

Any help is appreciated. Thank you!


=text(a1,"00")&text(b1,"0000")&text(c1,"00")


--ron

Try:

=TEXT(A1,"00")&TEXT(B1,"0000")&TEXT(C1,"00")

"Ash" wrote:

I am trying to concantenate three columns of data that contain numbers.

The first column has 2 numbers, the second has 4, and the third has 2. When
I combine the three nubmers they form a budget group, so all digits are
necessary.

In the second and third column, many have lead zeroes, or even have all zero
digits. When using the Concatenate or & function, it eliminates the leading
zeroes or leaves out a column if it is all zeroes.

Is there a way around this?

Ex: Column 1: 14 Column2: 0098 Column 3: 15
Currently: =A1&B1&C1 gives 149815 versus the needed 14009815

Any help is appreciated. Thank you!

When I tried formatting it as text it got rid of all the leading zeroes. I
appreciate your help though!

I used the formula posted by Topper and it seems to work. Thank again.

"tim m" wrote:

Do you need to do any mathematical calculations with the numbers? You could
format all the numbers as text and then use the same forumla as you used
before, it should then show the zeros.

"Ash" wrote:

I am trying to concantenate three columns of data that contain numbers.

The first column has 2 numbers, the second has 4, and the third has 2. When
I combine the three nubmers they form a budget group, so all digits are
necessary.

In the second and third column, many have lead zeroes, or even have all zero
digits. When using the Concatenate or & function, it eliminates the leading
zeroes or leaves out a column if it is all zeroes.

Is there a way around this?

Ex: Column 1: 14 Column2: 0098 Column 3: 15
Currently: =A1&B1&C1 gives 149815 versus the needed 14009815

Any help is appreciated. Thank you!

Worked like a charm! Thank you.

"Toppers" wrote:

Try:

=TEXT(A1,"00")&TEXT(B1,"0000")&TEXT(C1,"00")

"Ash" wrote:

I am trying to concantenate three columns of data that contain numbers.

The first column has 2 numbers, the second has 4, and the third has 2. When
I combine the three nubmers they form a budget group, so all digits are
necessary.

In the second and third column, many have lead zeroes, or even have all zero
digits. When using the Concatenate or & function, it eliminates the leading
zeroes or leaves out a column if it is all zeroes.

Is there a way around this?

Ex: Column 1: 14 Column2: 0098 Column 3: 15
Currently: =A1&B1&C1 gives 149815 versus the needed 14009815

Any help is appreciated. Thank you!