keep leading zeros on concatenate problem

ftalbot

New member
Joined
Sep 18, 2012
Messages
1
Reaction score
0
Points
0
I have data with variable length and variable number of leading zeros. I'm trying any type of concatenation or formula to keep the zeros. The cells are formated as custom ########0. Values look like this:
aaa 123456
bbb 005005
ccc 043434
ddd 000505
I want to concatenate and get exactly the values without dropping or adding zeros like 'ddd 000505' or 'aaa 123456'
I've tried format cells to text and it drops the zeros. I've tried = "'" & A1.
Everything I try drops the zeros or adds one zero when none are there.

Any ideas?

Thanks,

Frank
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
ARe those zeroes in the data as a text string, or is it just formatted that way?
 

FrankTa

New member
Joined
Sep 18, 2012
Messages
1
Reaction score
0
Points
0
Hi Bob thanks for the reply,

My data example is not correct. I have variable length data with leading zeros. Data can have no leading zero, 1 zero, and sometimes more that 1 zero. I want to keep all of the zeros because my DB2 database has a text field with the leading zeros. If I strip out the zeros I will not be able to find the index in DB2. All I can tell you about the spreadsheet is that the zeros are there and that the cells are formated as Custom ########0. If I change the cells to text it strips the zeros. I'm using office 2010.

Data looks like this.

aaa 012345
bbb 00050556
ccc 0012367
ddd 052525252
eee 529820343

I need to concatenate the 'numeric' text field and keep the zeros. End result will eventually be '052525252' and '0012367' so it would work to put apost around the data as well but everything I try strips out the zeroes.

Frank
 
Top