trying to concatenate two columns without removing leading zeros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

robr - 28 Jun 2007 17:17 GMT

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!

Reply to this Message

robr - 28 Jun 2007 17:25 GMT

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"))

> 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
[quoted text clipped - 5 lines]
> the cells as text first without any luck. Any help would be greatly
> appreciated. Thanks!

Reply to this Message

Bernie Deitrick - 28 Jun 2007 17:32 GMT

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

>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
[quoted text clipped - 5 lines]
> the cells as text first without any luck. Any help would be greatly
> appreciated. Thanks!

Reply to this Message

JohnR - 28 Jun 2007 17:35 GMT

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

>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
[quoted text clipped - 5 lines]
> the cells as text first without any luck. Any help would be greatly
> appreciated. Thanks!

Reply to this Message

robr - 28 Jun 2007 17:44 GMT

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.

> Formatting a1 and a2 as text should work. Formatting the cell that contains
> "=a1&a2" as text will not work
[quoted text clipped - 8 lines]
> > the cells as text first without any luck. Any help would be greatly
> > appreciated. Thanks!

Reply to this Message