Split Column at comma

Would there be a formula for splitting a line like the following into separate cells:

rear handle assy, replaces 20870-65100, 20870-65101, 20870-65102

Below is what I want it to look like: (COL indicates column separators, for example only, not to be used)
Instead of:

(COL1) rear handle assy, replaces, 20870-65100, 20870-65101, 20870-65102

Should be:

(COL1) rear handle assy (COL2) replaces 20870-65100, 20870-65101, 20870-65102

Basically, the formula could define the separating point by splitting "replaces" and everything after that into a new column. PLEASE NOTE that the comma after "assy" has been removed in the finished example...that's how I want it, if possible. I don't want that first comma there after the line has been split.

Thanks in advance, I appreciate any help very much, as I'm an Excel novice.

Originally Posted by KarambaStar

Would there be a formula for splitting a line like the following into separate cells:


Below is what I want it to look like: (COL indicates column separators, for example only, not to be used)
Instead of:


Should be:


Basically, the formula could define the separating point by splitting "replaces" and everything after that into a new column. PLEASE NOTE that the comma after "assy" has been removed in the finished example...that's how I want it, if possible. I don't want that first comma there after the line has been split.

Thanks in advance, I appreciate any help very much, as I'm an Excel novice.

Hi,
Try text to columns,
Select the cell, then goto data,text to columns, there you can select where you want the text to be split,

Try this:

Go to Edit and there choose "Find": "replaces"

Replace (this is the find/replace from the edit tab) "replaces" by *replaces
You should now have this in the cell.

rear handle assy, *replaces 20870-65100, 20870-65101, 20870-65102

Select the cells you want, go to "data", "text to columns" and choose "delimited" by "other" and choose symbol *

You can use the find/replace option again to get rid of the ending comma in the first column.

Why dont you try replace:

", replaces"

with

"*replaces"

and then use the text to columns as before.

(if there are more that one space between the comma and "replaces", use the "Trim" function to your data and then use the procedure above)