Moving some text to the next cell

[thedrizzle]

Hi all, I've got a large data set (roughly 1,500 cells) that I need to split out. They are currently formatted like this:

[TABLE="width: 410"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CHICOPEE, MA TO Rocky Mount, NC[/TD]
[/TR]
[TR]
[TD]MCDONOUGH, GA TO ROCKY MOUNT, NC[/TD]
[/TR]
[TR]
[TD]ATLANTA, GA TO ORLANDO, FL

I'm trying to separate this into four cells - City, St, City, St. Each cell is split in half with the word "TO", but I can't figure out how to separate everything after the word "TO" into the next cell since they're all different lengths. Once I do separate them, I can use the delimiter on the comma to separate each city from its state.

Anybody know how to do this?[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

You COULD do a find/replace and change " TO " to ", " and then do text-to-columns.

 

[Peter_SSs]

Welcome to the MrExcel board!

This could be done directly by formulas, but this may suit you better?

Select the column & do a Find/Replace: Find: " TO " (without the quotes but with the surrounding spaces) & a comma in the Replace with box & ensure 'Match entire cell contents' is not checked in Options.
Do another Find/replace to replace comma space with just comma
Now do Text to Columns with comma as the delimiter.

 

[Peter_SSs]

If you might be interested in a formula approach, each formula below copied down.

Excel Workbook
	A	B	C	D	E
1	CHICOPEE, MA TO Rocky Mount, NC	CHICOPEE	MA	Rocky Mount	NC
2	MCDONOUGH, GA TO ROCKY MOUNT, NC	MCDONOUGH	GA	ROCKY MOUNT	NC
3	ATLANTA, GA TO ORLANDO, FL	ATLANTA	GA	ORLANDO	FL
Split text

 

[thedrizzle]

Peter, this worked perfectly, thank you so much. i even got the delimiting done in one step by setting the comma and the colon both as delimiters.

 

[thedrizzle]

thank you, this worked well, I changed to " TO " to a ":" and then just delimited from there. Much appreciated

 

[Peter_SSs]

i even got the delimiting done in one step by setting the comma and the colon both as delimiters.

What colon?

Edit: Ah, I see from your last post.

 

[thedrizzle]

Oh yeah sorry I replaced the " TO " with a colon instead of a comma.

 

[Peter_SSs]

It may not matter to you but if you didn't do the second Find/replace I mentioned (replace comma space with comma) then most of your results will have leading or trailing spaces.

 

[Peter_SSs]

Oh yeah sorry I replaced the " TO " with a colon instead of a comma.

But really that just complicates (only slightly, I know) the TTC as you have to specify 2 delimiters instead of just 1.