# Thread: Mid - Find Issue

1. ## 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.  Reply With Quote

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.  Reply With Quote

3. 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))));"");""))  Reply With Quote

#### Posting Permissions

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