Mid with no set length

[allredkj]

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

[eisayev]

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

[Zack Barresse]

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

=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