Split string based on matching

gripper

Board Regular
Joined
Oct 29, 2002
Messages
164
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,315
Office Version
  1. 365
Platform
  1. Windows
Using the SUBSTITUTE formula should do what you want.
In another column, for row 1, the formula would look like:
Code:
=SUBSTITUTE(C1,D1,", " &D1)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,297
Messages
5,836,479
Members
430,434
Latest member
whatabout

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top