Results 1 to 3 of 3

Thread: Mid - Find Issue

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

    Mid - Find Issue



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

    I was wondering if you could help me fix my formula:

    =MID(V65, FIND("LOT",V65)+2,6)


    Here is a sample of the data:
    BLK 77 PT LT 11 14.7X100
    BLK 122/77 PT LTS 11 & 12
    PT LOTS 5 & 6-55X48.5X59X81X100
    BLK C/93 LOT 4 97.7X100.19X94.55X
    BLK A LOT 28

    Expecting to see this output:
    11
    11 & 12
    5 & 6
    4
    28

    Your assistance is appreciated.

  2. #2
    Can't see a formula managing that. Even VBA would be hard as there are multiple lookups, and the end of the data to be extracted is not clearly delineated.

  3. #3
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    31
    Articles
    0
    Here is your answer. Hope this will help you
    =IF(IFERROR(IF(FIND("&";A1;1)>1;MID(A1;FIND("&";A1;1)+2;LEN(TRIM(MID(A1;FIND(" ";A1;IFERROR(FIND("LT";A1;1);FIND("LOT";A1;1)))+1;2))));"");"")="";MID(A1;FIND(" ";A1;IFERROR(FIND("LT";A1;1);FIND("LOT";A1;1)))+1;2);MID(A1;FIND(" ";A1;IFERROR(FIND("LT";A1;1);FIND("LOT";A1;1)))+1;2)&" & "&IFERROR(IF(FIND("&";A1;1)>1;MID(A1;FIND("&";A1;1)+2;LEN(TRIM(MID(A1;FIND(" ";A1;IFERROR(FIND("LT";A1;1);FIND("LOT";A1;1)))+1;2))));"");""))

Posting Permissions

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