+ Reply to Thread
Results 1 to 11 of 11

Formula to extract text from a certain point

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,129

    Formula to extract text from a certain point

    I need a formula to extract text as follows in Col A


    eg Transfer from BR1 Call to BR4 Call********************


    1) In Col B In need to always extract the text starting from "to" onwards eg to BR4 call
    2) In Col C In needs tom extract text "from" up to the first text containing "Call" eg from BR1 call


    I have attached sample data as well as what the data should be in Col B & C


    It would be appreciated if someone could assist me
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,226

    Re: Formula to extract text from a certain point

    for the 1st 1...
    =MID(A1,SEARCH("to",A1),999)

    for the 2nd part
    =LEFT(MID(A1,SEARCH("From",A1),99),SEARCH("call",MID(A1,SEARCH("From",A1),99))-2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Formula to extract text from a certain point

    Iíd use this for the second requirement:

    =SUBSTITUTE(MID(A1,FIND("from",A1),99),B1,"")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,129

    Re: Formula to extract text from a certain point

    Thanks for the Help Ford


    If text to appears in other text for eg BR1 Auto call to BR4 call, then the first formula must extract the "to" after when the first call appears


    I have highlighted the item in B1 how it should appear (your formulas are in Col D & E


    Kindly amend your formula in Col D & E so that they give the sme result as my manual extract in Col B & C
    Attached Files Attached Files
    Last edited by Howardc1001; 08-02-2020 at 01:44 AM.

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,129

    Re: Formula to extract text from a certain point

    Thanks for the help Ali

    I have put your formula in Col F


    The result should be the same as in Col C


    Please check & amend
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Formula to extract text from a certain point

    Try this:

    =TRIM(MID(A1,SEARCH(" to",A1),99))

    See post #3 for the second option - a shorter version.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Formula to extract text from a certain point

    You canít have it both ways: you seem to want to exclude auto but include fast frt - this is illogical.

  8. #8
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,129

    Re: Formula to extract text from a certain point

    Thanks Ali

    I have put this in Col G. The first 2 results are perfect, but G3 give #value!

    Kindly check & amend
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Formula to extract text from a certain point

    I canít see any error messages in that file.

    First, you need to use the correct formula in B1. Then try this in C1:

    =SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("from",A1),99),B1,"")," auto","")

    This is a case of creeping goalposts. Why donít you just state clearly what you want and what you donít want instead of hinting and then expecting us to constantly tweak?

    Iím out.

  10. #10
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,129

    Re: Formula to extract text from a certain point

    My apologies Ali for the changes

    I overlooked it

    The formula works perfectly now

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Formula to extract text from a certain point

    That's good news.

+ 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. Extract text between one consistent point and another inconsistent point.
    By HappyChappy120 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-20-2019, 12:13 PM
  2. Formula to wrap text at a certain point
    By anasttin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2019, 08:00 PM
  3. Replies: 4
    Last Post: 04-19-2016, 05:19 PM
  4. [SOLVED] Extract text from a given point in a text string, when data points do not share the given
    By reedersketer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2014, 03:57 PM
  5. Using Find or Search to extract data starting point from a formula
    By mike_vr in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-08-2014, 12:31 PM
  6. [SOLVED] Extract Text Elements in a Cell providing stnd Starting/Stopping point.
    By Miskondukt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-13-2014, 02:48 PM
  7. Formula To Split Text at a Certain Point
    By steiney in forum Excel General
    Replies: 2
    Last Post: 04-07-2011, 12:55 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