trying to concatenate two columns without removing leading zeros

[robr]

A customer sent me a spreadsheet in an incorrect format for my
purposes. I have about 13,000 records in Excel 2007 where I need to
concatenate column 1 with column 2 to create the format I really need.

The problem is that each of those columns contains numbers, and when I
do an =a1&a2, if a1 contains 00123 and a2 contains 0121, I end up with
123121 when what I really need is 001230121. I've tried formatting
the cells as text first without any luck. Any help would be greatly
appreciated. Thanks!

 

[robr]

I'm not positive yet, but I *THINK* this may have worked. Let me see
if when I output the file to tab delimited, it saves the leading
zeros.

=CONCATENATE(TEXT(A6,"000"),TEXT(B6,"0000000"))

 

[Bernie Deitrick]

Robr,

You could use a custom UDF:

Function ConCatText(myC1 As Range, myC2 As Range) As String
ConCatText = myC1.Text & myC2.Text
End Function


Used like:
=ConCatText(A1,A2)

Copy the code and place it into a standard code module in the workbook.

HTH,
Bernie
MS Excel MVP

 

[JohnR]

Formatting a1 and a2 as text should work. Formatting the cell that contains
"=a1&a2" as text will not work
John

 

[robr]

Formatting as text is the very first thing I tried, as soon as I did
that, the leading zeros were stripped. The solution I posted worked
out. Bernie, thanks for your suggestion as well, though I didn't try
it I appreciate your time.