+ Reply to Thread
Results 1 to 12 of 12

Formula to extract particular range of text from a cell

  1. 01-29-2014, 09:40 AM #1
    faizzsheikh
    faizzsheikh is offline
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    61

    Formula to extract particular range of text from a cell

    Someone please help me with the below scenario:

    A1: The War of Art by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook
    A2: Do the Work by Steven Pressfield (Apr 20, 2011) - Kindle eBook
    A3: Nikola Tesla: Imagination and the Man That Invented the 20th Century by Sean Patrick (Apr 9, 2013) - Kindle eBook

    I want to extract text from RIGHT till the opening bracket "("

    So the result must be:
    B1: Nov 11, 2011) - Kindle eBook
    B2: Apr 20, 2011) - Kindle eBook
    B3: Apr 9, 2013) - Kindle eBook
    Reply With QuoteRegister To Reply
  2. 01-29-2014, 09:43 AM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Formula to extract particular range of text from a cell

    =right(a1, len(a1)-find("(", a1))

    If you want to include that parenthesis
    =RIGHT(A1, LEN(A1)-FIND("(", A1)+1)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 01-29-2014, 09:45 AM #3
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Formula to extract particular range of text from a cell

    In B1 cell

    =IFERROR(TRIM(MID(A1,FIND("(",A1)+1,5^5)),"")

    Drag it down...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  4. 01-29-2014, 09:51 AM #4
    AlKey
    AlKey is offline
    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: Formula to extract particular range of text from a cell

    in B1

    =REPLACE(A1,1,FIND("(",A1),"")
    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
    Reply With QuoteRegister To Reply
  5. 01-29-2014, 10:03 AM #5
    faizzsheikh
    faizzsheikh is offline
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Formula to extract particular range of text from a cell

    The formula is working with the cell which have more than one opening bracket.
    For example:

    A1: El Quinto Acuerdo (Un Libro De Sabiduria Tolteca) (A Toltec Wisdom Book) (Spanish Edition) by Don Miguel Ruiz, Don Jose Ruiz and Janet Mills (Jul 7, 2011) - Kindle eBook

    Returns

    B2: Un Libro De Sabiduria Tolteca) (A Toltec Wisdom Book) (Spanish Edition) by Don Miguel Ruiz, Don Jose Ruiz and Janet Mills (Jul 7, 2011) - Kindle eBook


    I really appreciate your help!
    Last edited by faizzsheikh; 01-29-2014 at 10:09 AM.
    Reply With QuoteRegister To Reply
  6. 01-29-2014, 10:08 AM #6
    AlKey
    AlKey is offline
    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: Formula to extract particular range of text from a cell

    Hi faizzsheikh,

    Unfortunately, things like this do happen mainly because the requester failed to provide accurate requirements.
    Reply With QuoteRegister To Reply
  7. 01-29-2014, 10:09 AM #7
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Formula to extract particular range of text from a cell

    You didn't give an example of one that had more than one opening bracket.

    =RIGHT(A1, LEN(A1)-FIND("^", SUBSTITUTE(A1,"(","^",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))+1)
    Reply With QuoteRegister To Reply
  8. 01-29-2014, 10:12 AM #8
    faizzsheikh
    faizzsheikh is offline
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Formula to extract particular range of text from a cell

    @Alkey

    I apologize... I should have been more clear.

    Is there a way to do it?
    Reply With QuoteRegister To Reply
  9. 01-29-2014, 10:15 AM #9
    faizzsheikh
    faizzsheikh is offline
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Formula to extract particular range of text from a cell

    Thanks a lot, ChemistB... it worked!!!

    Thanks to all who responded!
    Reply With QuoteRegister To Reply
  10. 01-29-2014, 10:28 AM #10
    AlKey
    AlKey is offline
    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: Formula to extract particular range of text from a cell

    try this

    =SUBSTITUTE(A1,LEFT(A1,FIND("^",(SUBSTITUTE(A1,"(","^",LEN(A1)-LEN(SUBSTITUTE(A1,"(","")))))),"")
    Reply With QuoteRegister To Reply
  11. 01-29-2014, 01:21 PM #11
    faizzsheikh
    faizzsheikh is offline
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Formula to extract particular range of text from a cell

    I need another assistance. This time I want the entire text before 'by'.

    For example:

    A1: The War of Art by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook
    A2: Do the Work by Steven Pressfield (Apr 20, 2011) - Kindle eBook
    A3: How to Get the Raise You Want in 90 Days or Less: A Step by step Plan for Making It Happen by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook (Note: This cell (A3) have "by" 2 times)


    So the result must be:

    B1: The War of Art
    B2: Do the Work
    B3: How to Get the Raise You Want in 90 Days or Less: A Step by step Plan for Making It Happen


    I really appreciate your help.

    (Probably we can replace the first 'By' from the right with a special character (|) and the apply delimit to segregate the data?)
    Reply With QuoteRegister To Reply
  12. 01-29-2014, 02:02 PM #12
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Formula to extract particular range of text from a cell

    Try this

    =LEFT(A1, FIND("^", SUBSTITUTE(A1," by ","^",(LEN(A1)-LEN(SUBSTITUTE(A1," by ","")))/4))-1)
    note there are spaces to each side of by inside the quotes
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Check if text 'A' in a cell occurs in the alphabet to extract its range from 'A-G','H-N'
    By jidef in forum Excel General
    Replies: 2
    Last Post: 03-18-2013, 12:43 PM
  2. [SOLVED] Formula to Extract Numbers from Text Cell
    By MHamid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2012, 04:08 PM
  3. extract number and use in formula from text & numbers in cell
    By ivory_kitten in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-14-2006, 12:45 AM
  4. [SOLVED] formula to extract partial content (text) of cell
    By milano in forum Excel General
    Replies: 3
    Last Post: 11-09-2005, 01:00 PM
  5. Formula to extract a phone# from a cell of text (eg classified)?
    By Reuel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2005, 04:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1