Separating Information in Cells

Alright, I have rows of cells with the following information..

Row 1, Column 1: Address City/State
Example: 111 Old Creek Road Stanton, VA 25523


Is there any way to separate the Address and the City/State? I would much rather the spreadsheet be in the following format.

Row 1, Column 1: Address
Row 1, Column 2: City/State

or ..

Row 1, Column 1: 111 Old Creek Road
Row 1, Column 2: Stanton, VA 25523

I'm just wondering if there's a fast way to do this, or if I have to go in and do it manually - the reason i'm looking for a faster way to do this is because I have approximately 3000 rows to do it to. :shrugs:

Thanks for your help.

You may well be able to - it depends on whether there is a constant format.

In your example you have a comma separating the items - using DATA > text to columns you could split these easily.

if there is no constant 'rule' it will be more complex.


Ed

There's no constant in the cells. So maybe the best method would be to go into each cell and have a comma separate the information that I want placed into different columns.

My only concern is that the information in the cell(s) is currently in this form: 111 Old Creek Road Stanton, VA 25523

If I were to use a comma like this: 111 Old Creek Road, Stanton, VA 25523 .. what's going to happen to Stanton, VA 25523 since there is a comma there as well?

Sorry if i'm missing something, just not extremely familiar with this program and only know the basics.

Hi xKylex,

Quote

HTML Code: 

If your information is in this format there is no problem - my suggestion will work.

Quote

Please Login or Register  to view this content.

This would end up in three colums.

I would suggest that you could manually get it all in the same format and then apply the solution.

Ed

Originally Posted by EdMac

Hi xKylex,

Quote

HTML Code: 

If your information is in this format there is no problem - my suggestion will work.

Quote

Please Login or Register  to view this content.

This would end up in three colums.

I would suggest that you could manually get it all in the same format and then apply the solution.

Ed

Well - what I'm trying to separate is <b>111 Old Creek Road</b> and <b>Stanton, VA 25523</b> into two separate columns.

I've sort-of got it figured out right now.. how will i apply your solution?

This might be useful,

assumes that you always have comma space and state abbreviation following the city name, won't work if you have a two word city, e.g. Los Angeles

Assuming your data is in A1 use this formula in B1

=LEFT(A1,LOOKUP(SEARCH(", ?? ",A1)-1,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))-1)

and this in C1

=TRIM(SUBSTITUTE(A1,B1,""))

copy formulas down columns

I'd go with DLL's solution - it's going to be the most effective

Ed

One problem, I'm going to have quite a few cities containing a space..

Example: Newport News, Virginia Beach, etc.

What can I do? :shrugs:

And let me clarify the purpose of this Excel document is to mail merge and I'm trying to separate these two since when you're mailing something it has to be in the following format ..

111 Old Creek Road
Stanton, VA 25523

AND NOT ..

111 Old Creek Road Stanton, VA 25523