Removing duplicate phrases from corresponding columns

[blackystrat]

Hello


I have a spreadsheet that have 2 columns; Address and City


The addresses end with the city names attached to it, but I also have a separate column with the city names.

Address	City
11305 Via Balboa Anchorage	Anchorage
P.O. Box 3652 35670 Kenai Spur Hwy. Ste. 102 Soldotna	Soldotna
P.O. Box 2721 Soldotna	Soldotna

<tbody>
</tbody>

What I want is to batch remove all the city names that are appended to the addresses in the same cell. I want to have two distinct columns for address and city name.

Any help with this would be greatly appreciated

Many thanks

 

[63falcondude]

Try making a new column (I'll say column C). Paste this formula in C2

=TRIM(SUBSTITUTE(A2,B2,""))

Source: Post #3 http://www.mrexcel.com/forum/excel-...ula-remove-text-address-cell.html#post4555600

 

[blackystrat]

Thank you very much for your help.

 

[63falcondude]

Glad to help.