How do I combine two EXCEL columns?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert Judge - 10 Oct 2006 00:54 GMT

I have an EXCEL 2003 worksheet containing addresses. The street address
information is in two columns. For instance, for the address 123 Main Street,
the worksheet has one column containing "123" and another column containing
"Main St."

How do I combine the two columns so that I have a new column that contains
"123 Main Street"? I will appreciate advice.

Reply to this Message

Pete_UK - 10 Oct 2006 01:13 GMT

Assume the house number is in column A and the street name in column B
- enter this formula in column C:

=A1 & " " & B1

and copy down for as many items as you have in columns A and B. Fix the
values by highlighting column C then <copy> and Edit | Paste Special |
Values (check), OK and <Esc>. You could now delete the original columns
A and B.

Hope this helps.

Pete

Hope this helps.

Pete

> I have an EXCEL 2003 worksheet containing addresses. The street address
> information is in two columns. For instance, for the address 123 Main Street,
[quoted text clipped - 3 lines]
> How do I combine the two columns so that I have a new column that contains
> "123 Main Street"? I will appreciate advice.

Reply to this Message

Gord Dibben - 10 Oct 2006 01:51 GMT

And then when you want to sort by street, you can break it into two columns
again using Data>Text to Columns.

My advice..............leave it two columns.

Your lfe will be easier down the road.

Gord Dibben MS Excel MVP

>Assume the house number is in column A and the street name in column B
>- enter this formula in column C:
[quoted text clipped - 21 lines]
>> How do I combine the two columns so that I have a new column that contains
>> "123 Main Street"? I will appreciate advice.

Reply to this Message

Robert Judge - 17 Oct 2006 16:23 GMT

Thank you. I used:

=A1&""&B1

However, I am getting "123Main St." instead of what I want, which is "123
Main St." How do I change the forumula to insert a space between "123" and
"Main"?

> Assume the house number is in column A and the street name in column B
> - enter this formula in column C:
[quoted text clipped - 21 lines]
> > How do I combine the two columns so that I have a new column that contains
> > "123 Main Street"? I will appreciate advice.

Reply to this Message

David Biddulph - 17 Oct 2006 17:11 GMT

If you'd used the formula you were given by Pete you would have been OK.
You need the space between the quote marks.
=A1&" "&B1
not
=A1&""&B1

Signature

David Biddulph

> Thank you. I used:
>
[quoted text clipped - 4 lines]
> and
> "Main"?

>> Assume the house number is in column A and the street name in column B
>> - enter this formula in column C:
[quoted text clipped - 5 lines]
>> Values (check), OK and <Esc>. You could now delete the original columns
>> A and B.

>> > I have an EXCEL 2003 worksheet containing addresses. The street
>> > address
[quoted text clipped - 7 lines]
>> > contains
>> > "123 Main Street"? I will appreciate advice.

Reply to this Message