Split string based on matching

[gripper]

I have a spreadsheet with addresses. They are formatted poorly and I am trying to clean them up.

What I want to do is split the text string and add in a space and comma. The split will be based on the value of another cell

For instance in Column C I have data that looks like this:

123 North MichiganManton, MI 49663
456 E. Main StMarthasville, MO 63357
758 ThayerButte, NE 68722

In column D I have the cities listed as such

Manton
Marthasville
Butte

What I would like to do is either in its existing Column C or another column copy the data but with the name of the city in Column D find that match in column C and add a ", " (comma and space) in front of the city name within this string. This will clean up the data and make it usable.

So the outcome when finished would be;

123 North Michigan, Manton, MI 49663
456 E. Main St, Marthasville, MO 63357
758 Thayer, Butte, NE 68722

Your thoughts would be greatly appreciated.

Thank you

 

[Joe4]

Using the SUBSTITUTE formula should do what you want.
In another column, for row 1, the formula would look like:

=SUBSTITUTE(C1,D1,", " &D1)