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.

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

Robert Judge wrote:
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.

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

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

Your lfe will be easier down the road.


Gord Dibben MS Excel MVP

On 9 Oct 2006 17:13:32 -0700, "Pete_UK" wrote:

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

Robert Judge wrote:
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.

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

"Pete_UK" wrote:

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

Robert Judge wrote:
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.

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
--
David Biddulph

"Robert Judge" wrote in message
...
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"?

"Pete_UK" wrote:

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.

Robert Judge wrote:
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.