IF function with long list in separate column

crayhead

New Member
Joined
Jun 6, 2016
Messages
10
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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?
 
Last edited:
Upvote 0
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 _
 
Upvote 0
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.
 
Last edited:
Upvote 0
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

CellFormula
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top