Cleaning up address list, some are single cells, some are double

**satriani990** · 03-07-2013, 05:01 AM

First of all, hello to everyone on the forum!

My problem has to do with an list of over 22,000 addresses (don't want to think about going through it manually

)

The names are in column A and the addresses are in column B. The problem is, is that some of the addresses fit in one cell and others are spread over two (Oklahoma City, OK in one cell, 73034 in the one below it, for example.)

The names with corresponding addresses taking up two squares have been placed in merged cells, so that they line up with their addresses. Here is a picture below.

Capture.PNG

What I need is each address in one cell next to its address in one cell (to make it searchable, etc.) I thought I was being really clever when I copied Column A, then pasted the formatting onto Column B (so that the cells would be 1:1, one name cell, one address cell) but didn't realize I would be losing the information in the cells being merged.

Is there any way to solve this?

Thank you

**TMS** · 03-07-2013, 05:37 AM

You may be able to do it with a formula or you may need VBA.

I suggest that you post a sample workbook rather than a picture of it.

Regards, TMS

**satriani990** · 03-07-2013, 05:46 AM

Thanks for the help!
Here's a sample workbook to clarify.

ExcelForum Example.xlsx

**poorcloud** · 03-07-2013, 05:55 AM

Wrote a simple formula.

Just unmerge all the cells in column A

Then use my formula

**satriani990** · 03-07-2013, 06:07 AM

That is simple and perfect. Needed another pair of eyes. Thanks a lot!

**TMS** · 03-07-2013, 07:10 AM

And my variation on a theme:

=IF(AND(A1<>"",A2<>""),B1,IF(AND(A1<>"",A2=""),B1& " " & B2,""))

and drag down.

Copy and Paste Special | Values then filter and delete blanks in column A (or, with my formula, in the new address column)

Regards, TMS