Forum Discussion
Solved
How can I extract a variable value in a string of inconsistent text?
Hey Everyone,
I need to extract a string of characters in order to perform an XLOOKUP function. See attached spreadsheet. Example data I need to extract from can be seen in Column 1. The value I need returned is always going to start with "AP-" followed by a string of numbers. These could be 3 numbers long or more. This string can also be random within the text as it's not always at the start of the text or the last. I added the value that needs to be extracted into Column 2 for examples. How am I able to extract this data?
=LET(position1, FIND("AP-",[@[Column 1]]), position2a, IFERROR(FIND(" ", [@[Column 1]], position1), 1000), position2b, IFERROR(FIND(CHAR(10), [@[Column 1]], position1), 1000), position2c, LEN([@[Column 1]])+1, position2, MIN(position2a, position2b, position2c), MID([@[Column 1]], position1, position2-position1))
- =MID(A2, FIND("AP-", A2), LEN(A2))
=LET(position1, FIND("AP-",[@[Column 1]]), position2a, IFERROR(FIND(" ", [@[Column 1]], position1), 1000), position2b, IFERROR(FIND(CHAR(10), [@[Column 1]], position1), 1000), position2c, LEN([@[Column 1]])+1, position2, MIN(position2a, position2b, position2c), MID([@[Column 1]], position1, position2-position1))
