Concatenate - number zero

**lynnmc26** · 01-14-2011, 04:42 PM

Hi there

I have a spreadsheet with 6 columns;

The data in the columns for example are:

100 01 02 022 10 A3

I need to concatenate them, but when I do I lose the first '0' in the column and get the following result:

100122210A3

I need the result as: 100010202210A3

What do I need to change to get the zero back?

Thanks
Lynn

**ChemistB** · 01-14-2011, 04:59 PM

100 01 02 022 10 A3

Assuming 1st and 4th value to always be 3 digits, 2nd, 3rd and 5th to be 2 digits and 6th to be letter and single digit, values in A1,B1,C1,D1,E1,F1

=TEXT(A1,"000")&Text(B1,"00")&Text(C1,"00") &Text(D1,"000")&Text(E1,"00")& F1

Does that work for you?

**TechRetard** · 01-14-2011, 05:00 PM

Lynn, if your #s with leading zeros are always in the same column, you can use the text() function for those...

i.e.:

Please Login or Register  to view this content.

would give you 100010202210a3

so the "00" means the cell will always show with at least 2 digits...1 would be 01, 10 would still be 10, 001 would be 01, etc...edit the formula as you see fit