Results 1 to 3 of 3

Thread: Mid with no set length

  1. #1
    Neophyte allredkj's Avatar
    Join Date
    Sep 2012
    Location
    Fort Worth, TX
    Posts
    4
    Articles
    0

    Mid with no set length



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    31
    Articles
    0
    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. #3
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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
    Regards,
    Zack Barresse

Posting Permissions

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