Trying To Extract Multiple, Same Phrases Out Of A Cell

Hello,

I'm currently trying to extract multiple strings with the same phrases ( the serial #'s located in the example below) . I currently only have my formula set up to extract one serial # but I need to extract multiple if there are multiple serial #'s present in the cell. Formula and example below.

=TRIM(LEFT(SUBSTITUTE(MID(A2,B2,100),")",REPT(" ",100)),100)). A2 is column with statement that contains the phrases, B2 is the position of the first character of the phrase.

Example: Phosphorus (Serial ####-##) and Ammonia NH3 (Serial ####-##) (This is provided in lab handout)

There are some instances where I have up to 10 serial #'s. If you need any help understanding the question, I will try and clarify the best I can.

Thanks!

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

I believe I have attached it. Sorry if I didn't still new to this forum.

I changed the formula in C2 to this:

=IFERROR(SEARCH("Serial No",$B2),"")

and in D2 to this:

=IF(C2="","",TRIM(LEFT(SUBSTITUTE(MID($B2,C2,100),")",REPT(" ",100)),100)))

These cope with the situation if there is no Serial No in the text.

Then you can use these formulae in the cells stated:

E2: =IFERROR(SEARCH("Serial No",$B2,C2+1),"")

F2: =IF(E2="","",TRIM(LEFT(SUBSTITUTE(MID($B2,E2,100),")",REPT(" ",100)),100)))

Then you can copy this pair of cells across into G2, I2, K2, and so on, for as many Serial Numbers as you expect to deal with. and then you can copy the row of formulae down to where you have data in column B.

I've shaded the cells in the attached file, so you can see where the formulae are active.

Hope this helps.

Pete

Yes it does. Thank you so much!!

You're welcome - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Pete

Or, try this without helper

In C2, copied across to F2 and all copied down :

=IFERROR("Serial No "&-LOOKUP(1,-MID($B2,FIND("@",SUBSTITUTE($B2,"Serial No ","@",COLUMNS($A:A)))+9,{1,2,3,4,5,6,7,8})),"")

Regards
Bosco