+ Reply to Thread
Results 1 to 8 of 8

Extracting Text

  1. #1
    Forum Contributor
    Join Date
    01-31-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    191

    Extracting Text

    Hello All,

    I want to extract the words 0.1NHCl ; pH4.5 ; pH6.8 ; 5% EtOH ; 20% EtoH ; 40%EtoH

    from the following

    in 900mL of 0.1NHCl, Paddle, 100rpm, 37C by HPLC
    in 900mL of pH4.5, Paddle, 100rpm, 37C by HPLC
    in 900mL of pH6.8 l, Paddle, 100rpm, 37C by HPLC
    in 900mL of 5% EtOHl, Paddle, 100rpm, 37C by HPLC
    in 900mL of 10% EtoH, Paddle, 100rpm, 37C by HPLC
    in 900mL of 20%EtoH, Paddle, 100rpm, 37C by HPLC
    in 900mL of 40%EtoH, Paddle, 100rpm, 37C by HPLC

    Your help is much appreciated.

    Thank you.

  2. #2
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: Extracting Text

    try with this.

    =IFERROR(MID($A2,FIND(B$1,$A2),LEN(B$1)),"")

    Place the parts you are searching (0.1NHCl ; pH4.5 ; pH6.8 ; ...) in the first row (B1:G1).
    Place the test string in column A (A2:A8).
    Copy the formula in the range in between (B2:G8).

  3. #3
    Forum Contributor
    Join Date
    01-31-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Extracting Text

    Hi Igormigor,

    Sorry for not making myself clear. I dont want to find them at once.
    for example on this line

    in 900mL of 0.1NHCl, Paddle, 100rpm, 37C by HPLC

    I would like to find only 0.1NHCl

    Thank you.

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Extracting Text

    If your text is in A1, use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It relies on what you want to find being after the third space and before the first comma.


    Edit: this formula leaves a space in front of the result - see post 7 for corrected version.
    Last edited by Aardigspook; 01-22-2016 at 07:18 AM. Reason: Note correction needed to formula
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extracting Text

    In B1 and copy down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit: corrected formula

    v A B
    1 in 900mL of 0.1NHCl, Paddle, 100rpm, 37C by HPLC 0.1NHCl
    2 in 900mL of pH4.5, Paddle, 100rpm, 37C by HPLC pH4.5
    3 in 900mL of pH6.8 l, Paddle, 100rpm, 37C by HPLC pH6.8 l
    4 in 900mL of 5% EtOHl, Paddle, 100rpm, 37C by HPLC 5% EtOHl
    5 in 900mL of 10% EtoH, Paddle, 100rpm, 37C by HPLC 10% EtoH
    6 in 900mL of 20%EtoH, Paddle, 100rpm, 37C by HPLC 20%EtoH
    7 in 900mL of 40%EtoH, Paddle, 100rpm, 37C by HPLC 40%EtoH
    Last edited by AlKey; 01-21-2016 at 08:59 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: Extracting Text

    if you want to find only "0.1NHCl" use this formula

    =IFERROR(MID($A1,FIND( "0.1NHCl",$A1),LEN( "0.1NHCl" )),"")

    if you want to find another string, you can change the "0.1NHCl" part of the formula with the new string.

    Or if you want to have single formula to find all the strings you've mentioned in your first post, you can use AlKey formulas.
    Aardigspook formula is also good, but it creates empty space in front of the string.

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Extracting Text

    Quote Originally Posted by igormigor View Post
    Aardigspook formula ... creates empty space in front of the string.
    Good catch!
    The corrected formula, to fix that, is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or 'Trim' will remove any leading/trailing spaces:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Extracting Text

    =TRIM(MID(LEFT(A1,SEARCH(",",A1)-1),SEARCH("of",A1)+2,256))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ 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. Extracting numbers and text from inconsistent text/number formatted string
    By Brandivil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2013, 01:46 PM
  2. Extracting certain text from text string
    By Cicada in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2012, 11:29 AM
  3. [SOLVED] Extracting Text from a string of Text & Digits of variable length
    By hastex in forum Excel General
    Replies: 6
    Last Post: 06-06-2012, 09:11 AM
  4. Extracting portion of text text
    By speedbird168 in forum Excel General
    Replies: 4
    Last Post: 02-10-2012, 02:21 AM
  5. Extracting Text from a string of text between brackets
    By easty in forum Excel - New Users/Basics
    Replies: 11
    Last Post: 12-02-2009, 11:29 AM
  6. Extracting strings of text from Columns of data(text)
    By welshmagic69 in forum Excel General
    Replies: 9
    Last Post: 04-23-2007, 02:50 PM
  7. Extracting text from text string
    By Emile in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-30-2006, 03:50 PM
  8. Extracting Text in a string of text
    By walkerdayle in forum Excel General
    Replies: 5
    Last Post: 12-19-2005, 04:27 PM

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