Concatenate numbers and maintain the zeroes 1

[sap1958]

I concatenated the following:
0149 0783 02 using =concatenate(A1,A2,A3) I get the following number 1497832 All zeroes are removed. I need the zeroes even if it means converting to a text field. I need
0149078302. Concatenations of numbers removed the zeroes. How can I resolve this? I am using Excel 2007

 

[SkipVought]

Hi,

What you have are NUMBERS. NUMBERS do not have leading zeros. TEST (a string of digits can)

to make your numbers, stings of numeric digits, prefix an APOSTROPHY and add the ZEROs as required.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

I have wasted Years writing ="007" instead of that apostrophe. Thanks Skip, for the saved years ahead!

 

[Andrzejek]

You could also Format Cells (or entire columns) as Text...

Have fun.

---- Andy

 

[SkipVought]

You could also Format Cells (or entire columns) as Text...

[red]FORMATTING CHANGES NOTHING!!![/red]

FORMATTING is used to change the DISPLAY. The underlying value remains UNCHANGED.

Formatting a NUMBER as TEXT has no effect on the VALUE. The NUMBER VALUE must be CONVERTED to text.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

The NUMBER values in column A would seem to have a custom format code of "0##########" applied to force the leading zero to display.

As Skip notes, this formatting does not change the underlying value.

To keep the leading zeros after the concatenate you need to:

=CONCATENATE(TEXT(A1,"0########"),TEXT(B1,"0#######"),TEXT(C1,"0#######"))

which will result in a STRING.

 

[Andrzejek]

What I meant to say was: change colums (cells) to Text *BEFORE* entering the data into them. This way if the cell is text and you type in 0123 - you will have in it 0123, and not 123

Sorry for teh confusion... :-(

Have fun.

---- Andy