# Help with comparing two columns and pulling non-matching data

#### Bfish

##### New Member
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
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Special-K99

##### Well-known Member
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

##### Well-known Member
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

##### New Member
Thank you. This worked perfectly!!

#### Bfish

##### New Member
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

##### Well-known Member
Bfish,

Excel 2007
ABC
1100 Division Street100 Division Street AnsoniaAnsonia
21135 Famington Avenue1135 Famington Avenue BerlinBerlin
411 East High St.11 East High St. East HamptonEast Hampton
5
Sheet1
Cell Formulas
RangeFormula
C1=RIGHT(B1,LEN(B1)-LEN(A1)-1)

The formula in cell C1, copied down.

#### Bfish

##### New Member
Bfish,

Excel 2007
ABC
1100 Division Street100 Division Street AnsoniaAnsonia
21135 Famington Avenue1135 Famington Avenue BerlinBerlin
411 East High St.11 East High St. East HamptonEast Hampton
5

</tbody>
Sheet1

Worksheet Formulas
CellFormula
C1=RIGHT(B1,LEN(B1)-LEN(A1)-1)

</tbody>

<tbody>
</tbody>

The formula in cell C1, copied down.

That looks like it would work as well Hiker

#### hiker95

##### Well-known Member
Bfish,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

Replies
4
Views
376
Replies
1
Views
383
Replies
1
Views
377
Replies
13
Views
714
Replies
3
Views
355

1,191,587
Messages
5,987,506
Members
440,098
Latest member
MickyMouse123

### 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.

### Which adblocker are you using?

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

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