# Thread: Mid with no set length

1. ## Mid with no set length

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

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•