Extract multiple values from a single value that meet a single search string

Here’s a challenging one I haven’t been able to find on this forum or other sources I’ve scoured.

I have a text column I need to extract strings from. Each cell may contain zero to fifteen of the strings I need to extract. I’ve tried using & concatenations of text functions MID() and FIND() by increasing the starting increment of the FIND(). However, if subsequent FIND() statements do not have results, it returns #VALUE as expected. Suppose I could nest in IF statements, however the text cell could be up to 5000 characters and contain up to 15 strings for extraction. So, my solution is clunky to say the least.

In my attached example, you see I’m looking for the string “8000” to fetch the 10-digit 8000 series number. An acceptable solution would only anticipate 15 strings for retrieval. Do need a line-break for further data processing, hence the use of CHAR(10).

Thanks for taking a look and any suggestions you may have.

Hi njohnson,

Welcome to the forum.
See the attached file, green area which can be dragged towards right side in similar fashion to accommodate more numbers. thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

Okay, building on what you guys have already accomplished, this formula in C3 dragged to the right (and down)

=IF((LEN($A3)-LEN(SUBSTITUTE($A3,"8000","")))/4 < COLUMN(A1),"",MID($A3,FIND(8000,$A3,(SEARCH(B3,$A3,1)+10)),10))
The first part starts putting blanks into the cells once the # of 8000's in Col A cell is exceeded.
Hope that helps.

Hi njohnson,

It's good to hear that you are on right track.. and I see that few more forum members are getting interested to this and provided some solution as well.. now did you managed to solve it ?

Regards,
DILIPandey

<click on below 'star' if this helps>

<click on b