merging all data in range

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Wyza

Thread Starter
Joined
Jan 12, 2003
Messages
22
Two questions:

1. How can I insert a character at the end of every cell in a column?
2. (Most important) How can I merge two cells without losing the data in the second cell?

I have a worksheet with several columns for the addresses- Street, City, Province, Postal Code, etc. In the 'City' column I have Toronto, Calgary, Vancouver, etc. and in their adjacent cells, Ontario, Alberta, BC, etc.

I want to add a comma at the end of all the 'City' cells and then merge the 'City' and the 'Province' cells so "Toronto, Ontario" appears in one cell.

Right now, whenever I merge Toronto with Ontario, only 'Toronto' shows up in the merged cell.

Thanks folks for any help you can offer.
 
Joined
Jul 1, 2005
Messages
8,546
Let's say column B = "City", column C = "Province". One way would be to use a formula in another column ("City, Province") to concatenate (fancy word for "bolt together") the values.

So if B2 = Toronto & C2 = Ontario, the formula:

=B2&","&C2

would give you Toronto,Ontario.

You could copy such a formula as far as down as you have rows, then hard-code it (fancy term for "convert the formula results into actual values"). To do this, you copy the column, then use Edit -- Paste Special -- Values. Then you're safe to delete the original "City" and "Province" columns.
 
Joined
Jul 1, 2005
Messages
8,546
Another way would be to use a macro (programming code), such as:

Sub Combine_City_Province()
For Each Cell In Range("B2", Range("B2").End(xlDown))
Cell.Offset(0, 4) = Cell & "," & Cell.Offset(0, 1)
Next Cell
End Sub


This will loop through all cells from B2 down (there must be no blanks, and values in B2 and B3 at least) and create the required string (column B value + "," + column C value) in column F. The main advantage of this is you don't have to do the hard-coding afterwards.

This macro is included in the attached file. To run it, press ALT+F8, then doubleclick the macro name in the listbox.

Rgds,
Andy
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top