+ Reply to Thread
Results 1 to 7 of 7

Trying To Extract Multiple, Same Phrases Out Of A Cell

  1. #1
    Registered User
    Join Date
    09-19-2018
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    3

    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!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,555

    Re: Trying To Extract Multiple, Same Phrases Out Of A Cell

    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

  3. #3
    Registered User
    Join Date
    09-19-2018
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    3

    Re: Trying To Extract Multiple, Same Phrases Out Of A Cell

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

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,555

    Re: Trying To Extract Multiple, Same Phrases Out Of A Cell

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-19-2018
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    3

    Talking Re: Trying To Extract Multiple, Same Phrases Out Of A Cell

    Yes it does. Thank you so much!!

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,555

    Re: Trying To Extract Multiple, Same Phrases Out Of A Cell

    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

  7. #7
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    195

    Re: Trying To Extract Multiple, Same Phrases Out Of A Cell

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Extract unigue phrases from range of strings and count them
    By PaulM100 in forum Excel General
    Replies: 5
    Last Post: 05-17-2018, 10:10 AM
  2. [SOLVED] Search multiple phrases in a cell and return the first phrase found
    By pmeltzers in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-26-2017, 04:07 PM
  3. Search for multiple key phrases and paste into a new workbook
    By jyellis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2017, 08:16 PM
  4. [SOLVED] Help needed with VBA code to extract Phrases/ords from text string
    By rv02 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2015, 09:08 AM
  5. [SOLVED] VBA code required to Extract the content between 2 words/Phrases
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-13-2015, 01:20 AM
  6. Compare lists of phrases in two workbooks and import only missing phrases
    By Fireblejd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2011, 06:59 AM
  7. How do I search multiple separate phrases at once?
    By Dcrader in forum Excel General
    Replies: 4
    Last Post: 01-02-2009, 02:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1