Extract Text and Number in Varying position

SherryFox

I need to extract text and a number to provide me with my account number. In the screenshot below shows an example of the data I am faced with. The formula will go on this sheet shown. As you can see, sometimes there is no TDL #. Sometimes the TDL # is in the front, other times in the middle, and apparently at the end sometimes too. I did find variations where sometimes it was TDL and then the #, rather than the dash (2nd screenshot). Could this be factored in where the result would display with the dash? Like this example shown would be TDL-171. I only require the TDL # as my final result in the formula, so it should be TDL-170 or whatever the number is. I will be using this column to do an INDEX/MATCH.

Text: I need to extract Text ("TDL-")

Number: either 2 or 3 digit number.

Space: A space after the number

Kerry St. Thomas

Oh wow. What a puzzle! Here's what I've come up with. It is very much an incomplete solution, though, so I'll walk through the logic - I did not solve for EVERYTHING, because it starts to get more and more recursive. You'll know what your dataset can tolerat though.

=IFERROR(MID([Deliverable Name]@row, FIND("TDL-", [Deliverable Name]@row, 1), FIND(" ", [Deliverable Name]@row, FIND("TDL-", [Deliverable Name]@row, 1) + 1) - FIND("TDL-", [Deliverable Name]@row, 1)), "Investigate")

MID - pulls the number of characters out of [Deliverable Name]@row
FIND #1 - looks for position of "TDL-" starting at the beginning of the cell, and returns a number.
FIND #2 & #3 - looks for the first space AFTER the number in #1 above.
FIND #4 - position of space minus position of "TDL-" = the length to return
IFERROR - if you don't find TDL- …including the dash or something breaks, it returns a flag for further investigation.

You can apply the same logic for finding the TDL ### items, but based on the dataset size, you might want to do a manual review with this flag. I also EMPHATICALLY recommend that you put constraints in place to require people to input the TDL number with constraints using a form, and then separately the deliverable name, and then you rebuild the two together in the background.

Good luck!

Kerry St. Thomas

Oh wow. What a puzzle! Here's what I've come up with. It is very much an incomplete solution, though, so I'll walk through the logic - I did not solve for EVERYTHING, because it starts to get more and more recursive. You'll know what your dataset can tolerat though.

=IFERROR(MID([Deliverable Name]@row, FIND("TDL-", [Deliverable Name]@row, 1), FIND(" ", [Deliverable Name]@row, FIND("TDL-", [Deliverable Name]@row, 1) + 1) - FIND("TDL-", [Deliverable Name]@row, 1)), "Investigate")

MID - pulls the number of characters out of [Deliverable Name]@row
FIND #1 - looks for position of "TDL-" starting at the beginning of the cell, and returns a number.
FIND #2 & #3 - looks for the first space AFTER the number in #1 above.
FIND #4 - position of space minus position of "TDL-" = the length to return
IFERROR - if you don't find TDL- …including the dash or something breaks, it returns a flag for further investigation.

You can apply the same logic for finding the TDL ### items, but based on the dataset size, you might want to do a manual review with this flag. I also EMPHATICALLY recommend that you put constraints in place to require people to input the TDL number with constraints using a form, and then separately the deliverable name, and then you rebuild the two together in the background.

Good luck!

SherryFox

@Kerry St. Thomas ,

Outstanding! Gold Star for you!!!