IF function with long list in separate column

[crayhead]

OK forum, I can't figure out how to ask this question correctly in the search or Google to get a satisfactory answer. I am sure y'all will figure it out.

I have a text thread that starts out in a cell like this (pulled from the source like this, no way to change):
G1 Address
[TABLE="width: 394"]
<tbody>[TR]
[TD="width: 394"]G2 4208 Bluffs Lane Durham, North Carolina 27712


[/TD]
[/TR]
</tbody>[/TABLE]
I used some other people's formula knowledge and existing data sets to chop that up into its separate parts:
[TABLE="width: 558"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[/TR]
[TR]
[TD]4208 Bluffs Lane[/TD]
[TD]Durham[/TD]
[TD]NC[/TD]
[TD]27712[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 558"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The problem comes when the city is a two word city.
[TABLE="width: 558"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[/TR]
[TR]
[TD]1912 Foxcroft Drive Mount[/TD]
[TD]Airy[/TD]
[TD]NC[/TD]
[TD]27030[/TD]
[/TR]
</tbody>[/TABLE]

Then the first word of the city name is not being pulled because of the original function which only looks to pull the rightmost word (plus the comma that comes from the original data, shortened in a subsequent cell)
=RIGHT(I86,LEN(I86)-FIND("^^",SUBSTITUTE(I86," ","^^",LEN(I86)-LEN(SUBSTITUTE(I86," ","")))))

I86 contents are:
[TABLE="width: 394"]
<tbody>[TR]
[TD="width: 394"]1912 Foxcroft Drive Mount Airy,


[/TD]
[/TR]
</tbody>[/TABLE]

I'm sure improvements can be made on that formula but it does the trick most of the time. I only end up with a few entries that still have the city tacked on the end of the street address. For those few, the final city name is in column L. What I would like is an IF function that checks against a list of known two city names (like "Mount Airy" in the example) and either substitutes the two words for the one or - preferrably - goes back and trims the other words out of the new K column (I column minus the RIGHT second or only city name).

Suggestions for best way to do this? I coudn't figure it out using INDEX MATCH or like functions...

TIA

 

[ukmikeb]

Hi

Can you depend on the part of the address before the town always having a street name that has two parts?

Or indeed that the house number will always be just one string?

 

[mole999]

Not sure how many would be two words, but if you had a find replace VBA to take Mount Airy to Mount_Airy to pull it out, and when you use it use SUBSTITUTE to remove the _

 

[crayhead]

Hi

Can you depend on the part of the address before the town always having a street name that has two parts?

Or indeed that the house number will always be just one string?

As far as I can remember, it has always been "Easy Street" at minimum, so it will have at least two parts. However, it could also have three (Easy Street South) or more.

House number could be hyphenated or occasionally (with apartment addresses) you may have a letter thrown in also: "551-B" or "551 B".

Got sidetracked on a separate project. Thanks for responding. Looking forward to follow-up.

 

[ukmikeb]

Hi

I've noticed that you have a comma separating the Street/Town from the State/Zipcode and working on that basis
With two helper columns and a worksheet of Two word town names (NC_Towns) it is possible to split the addresses more easily, as follows :-
crayhead[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Address String[/TD]
[TD][/TD]
[TD]First Part[/TD]
[TD]Poss Town/City[/TD]
[TD]Town/City[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]4208 Bluffs Lane Durham, North Carolina 27712[/TD]
[TD][/TD]
[TD]4208 Bluffs Lane Durham[/TD]
[TD]Lane Durham[/TD]
[TD]Durham[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]1912 Foxcroft Drive Mount Airy, North Carolina 27030[/TD]
[TD][/TD]
[TD]1912 Foxcroft Drive Mount Airy[/TD]
[TD]Mount Airy[/TD]
[TD]Mount Airy[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

Cell	Formula
C2	=LEFT(A2,FIND(",",A2)-1)
D2	=SUBSTITUTE(RIGHT(SUBSTITUTE(C2," ",REPT("#",40),LEN(C2)-LEN(SUBSTITUTE(C2," ",""))-1),50),"#","")
E2	=IF(ISNUMBER(MATCH(D2,NC_Towns!$A$1:$A$12,0)),D2,TRIM(RIGHT(SUBSTITUTE($D2," ",REPT(" ",30)),30)))
C3	=LEFT(A3,FIND(",",A3)-1)
D3	=SUBSTITUTE(RIGHT(SUBSTITUTE(C3," ",REPT("#",40),LEN(C3)-LEN(SUBSTITUTE(C3," ",""))-1),50),"#","")
E3	=IF(ISNUMBER(MATCH(D3,NC_Towns!$A$1:$A$12,0)),D3,TRIM(RIGHT(SUBSTITUTE($D3," ",REPT(" ",30)),30)))

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]




NC_Towns[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Chapel Hill[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Elizabeth City[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Mount Airy[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Mount Gilead[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Mount Holly[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]Mount Olive[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]Mount Pleasant[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]Mountain Home[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]Mountain View[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]New Bern[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]Roanoke Rapids[/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]Rocky Mount[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

hth