Help with comparing two columns and pulling non-matching data

[Bfish]

Hello all sorry if this has been asked before but I could use some help. I have an excel sheet with two columns of data. Column A contains a street address, column B contains the street address plus the city name. I would like to pull the city name from column B and put it in column C OR just delete the street address from column B leaving just the city name(whichever would be easier). Example data below:

Column A Column B
100 Division Street 100 Division Street Ansonia
1135 Famington Avenue 1135 Famington Avenue Berlin
313 Cottage Grove Road 313 Cottage Grove Road Bloomfield
11 East High St. 11 East High St. East Hampton


As you can see the city will not always be just one word so just removing the last word won't work in this situation. Any help would be greatly appreciated.

 

[Special-K99]

This probably isnt going to work every time but might get you somewhere
Try

in C1
=TRIM(SUBSTITUTE(B1,A1,""))
and copy down the column

 

[adam087]

How will Excel, or indeed anyone, know what constitutes a city? Is there a definitive list somewhere it can be compared against? Or could we say for certain that the penultimate word, where it is part of the city, will be a limited number of words (East, West, Greater, etc).


If it relies on Excel making a judgement call then that might be tricky.


/AJ

 

[Bfish]

Thank you. This worked perfectly!!

 

[Bfish]

Adam, it wasn't a matter of excel figuring out what a city name is or isn't. The text in column B was exactly the same as the text in column A up until the start of the city name. So all I needed was the text from column B that was different then column A. Special's formula above worked great to accomplish this.

 

[hiker95]

Bfish,

How about something like this?

Excel 2007
	A	B	C
1	100 Division Street	100 Division Street Ansonia	Ansonia
2	1135 Famington Avenue	1135 Famington Avenue Berlin	Berlin
3	313 Cottage Grove Road	313 Cottage Grove Road Bloomfield	Bloomfield
4	11 East High St.	11 East High St. East Hampton	East Hampton
5
Sheet1

Cell Formulas
Range	Formula
C1	=RIGHT(B1,LEN(B1)-LEN(A1)-1)

The formula in cell C1, copied down.

 

[Bfish]

Bfish,

How about something like this?

Excel 2007

	A	B	C
1	100 Division Street	100 Division Street Ansonia	Ansonia
2	1135 Famington Avenue	1135 Famington Avenue Berlin	Berlin
3	313 Cottage Grove Road	313 Cottage Grove Road Bloomfield	Bloomfield
4	11 East High St.	11 East High St. East Hampton	East Hampton
5

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =RIGHT(B1,LEN(B1)-LEN(A1)-1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The formula in cell C1, copied down.

That looks like it would work as well Hiker

 

[hiker95]

Bfish,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.