Mid with no set length

A

allredkj

New member
Joined
Sep 14, 2012
Messages
4
Reaction score
0
Points
0
Location
Fort Worth, TX
I am trying to extract the street type portion from an address string. Following is a sample of the data:
0000899
0001108W Division St
0001231Colorado Ln
0002112W Division St
0001400Blue Danube St
0000359Russell Ln
0002313W Arkansas Ln # 111
0000615Stadium Dr

These are the expected results after the formula is applied:
(null row)
St
Ln
St
St
Ln
Ln
Dr

Your assistance is appreciated.

Thanks,
Kellu
 
The formula below considering there are only three street type:
=IFERROR(IFERROR(MID(A2;FIND("St";A2;1);2);MID(A2;FIND("Ln";A2;1);2));MID(A2;FIND("Dr";A2;1);2))
Thinking about different way to extract the street type. I will let you know if i will able to figure out
 
Without knowing what it will be, and to leave a null for numerics only, and not knowing if the pound sign (#) will show up (this is the hard part), you could use this as a truly dynamic formula...

Code: 
=IF(ISNUMBER(--(A1)),"",IF(ISNUMBER(FIND("#",A1)),MID(LEFT(SUBSTITUTE(A1," # ","|"),FIND("|",SUBSTITUTE(A1," # ","|"))-1),FIND("*",SUBSTITUTE(LEFT(SUBSTITUTE(A1," # ","|"),FIND("|",SUBSTITUTE(A1," # ","|"))-1)," ","*",LEN(LEFT(SUBSTITUTE(A1," # ","|"),FIND("|",SUBSTITUTE(A1," # ","|"))-1))-LEN(SUBSTITUTE(LEFT(SUBSTITUTE(A1," # ","|"),FIND("|",SUBSTITUTE(A1," # ","|"))-1)," ",""))))+1,999),MID(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,999)))

It's really only long because of the # sign. It will look for the last part of the text (A1) after the last space, with the conditions checked for, 1) all numerics, and 2) the # sign, which will ignore everything to the right of that character.

HTH
 
You must log in or register to reply here.
Back
Top