split data into 2 cells, text to columns

**ericc2728** · 02-26-2009, 02:03 PM

i have cells with city and state in them and i'm trying to separate the one column into two columns. the problem is, i'm trying to keep the city name in one column and the state in the other. some of my cells have two word cities like new albany, or upper arlington. the text to columns feature is separating those cells into 3 columns not 2. is there a way to do this?

example:
worthington, oh
upper arlington, oh

text to columns splitting upper arlington into 3 different cells because the only delimiter in the cell is a space. i need to keep upper arlington in one cell and oh in another. thanks for any help

**NBVC** · 02-26-2009, 02:12 PM

Is the comma in those cells?.. that can be used to delimit.

**Dunc3142** · 02-26-2009, 02:14 PM

Try this it is assuming that there is a comma.

**mrice** · 02-26-2009, 02:16 PM

This macro will split the selected text by the final space.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

**NBVC** · 02-26-2009, 02:21 PM

If there are no commas... 2 formulas...

in B1:

=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

in C1:

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

both copied down.

**ericc2728** · 02-26-2009, 02:23 PM

no commas, only space. i'll try some of your suggestions for a space

**ericc2728** · 02-26-2009, 02:44 PM

NBVC you are awesome, that was the exact formula i was looking for!
mrice - i tried your macro and for some reason it is carrying part of the city name over to the next cell. the format looks like this after running the macro

Column A Column B
Powell ell OH
Pickerington kerington OH
Williamston liamston MI
Johnstown nstown OH
Columbus umbus OH
Bellbrook lbrook OH
Columbus umbus OH
Anchorage horage AK
Dublin lin OH
Sylvania vania OH
New OH
Grove ty OH
Upper on OH
Westerville terville OH

**NBVC** · 02-26-2009, 03:03 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save