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.

[TABLE="width: 577"]
<tbody>[TR]
[TD]Address[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]11305 Via Balboa Anchorage[/TD]
[TD]Anchorage[/TD]
[/TR]
[TR]
[TD]P.O. Box 3652 35670 Kenai Spur Hwy. Ste. 102 Soldotna[/TD]
[TD]Soldotna[/TD]
[/TR]
[TR]
[TD]P.O. Box 2721 Soldotna[/TD]
[TD]Soldotna[/TD]
[/TR]
</tbody>[/TABLE]

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.