Results 1 to 7 of 7

Thread: Formula to Extract Certain Text

  1. #1
    Seeker Bassem's Avatar
    Join Date
    Mar 2017
    Posts
    9
    Articles
    0
    Excel Version
    2013

    Question Formula to Extract Certain Text



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    Would you please help in providing a formula to extract the text as shown in column (B)
    Thanks.

    A B
    1 Doe, John (511) John
    2 Mark, Alice (534) Alice
    3 Paul, Sarah (694) Sarah
    4 Ramsey Hanks, Mary (752) Mary

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,400
    Articles
    0
    Excel Version
    Office 365 Subscription
    This will work on your sample data:

    =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(",",A1)+2,100),"(",REPT(" ",99)),97))

    If your real data differs, then it may not work, but we can't provide a solution for what you haven't told us.

    Let us know, one way or the other, please.
    Last edited by AliGW; 2018-08-13 at 12:18 PM.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    164
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Bassem View Post
    Hi,
    Would you please help in providing a formula to extract the text as shown in column (B)
    Thanks.

    A B
    1 Doe, John (511) John
    2 Mark, Alice (534) Alice
    3 Paul, Sarah (694) Sarah
    4 Ramsey Hanks, Mary (752) Mary







    Being a far more lazier version of AliGW, take a look at flash fill (not to be confused with"Flash Dance" which I believe was a 1990's Movie...) it is exceedingly useful Excel addition (found in the Data Tab). Make sure the flash fill column is immediately adjacent to the last column of the table or data block, fill out, normally 2 items and the algorithm will pull out the desired data. So in your instance you would start the fill data column right next to your column A, type John and Alice on both rows and then click flash fill (or Control + E will do it). Think of Flash Fill as a form of power Query Lite.

  4. #4
    Seeker Bassem's Avatar
    Join Date
    Mar 2017
    Posts
    9
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by AliGW View Post
    This will work on your sample data:

    =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(",",A1)+2,100),"(",REPT(" ",99)),97))

    If your real data differs, then it may not work, but we can't provide a solution for what you haven't told us.

    Let us know, one way or the other, please.

    Thank you very much Ali, it works.

  5. #5
    Seeker Bassem's Avatar
    Join Date
    Mar 2017
    Posts
    9
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by Ed Kelly View Post
    Being a far more lazier version of AliGW, take a look at flash fill (not to be confused with"Flash Dance" which I believe was a 1990's Movie...) it is exceedingly useful Excel addition (found in the Data Tab). Make sure the flash fill column is immediately adjacent to the last column of the table or data block, fill out, normally 2 items and the algorithm will pull out the desired data. So in your instance you would start the fill data column right next to your column A, type John and Alice on both rows and then click flash fill (or Control + E will do it). Think of Flash Fill as a form of power Query Lite.
    Thank you very much Ed, I will check this as well.

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,400
    Articles
    0
    Excel Version
    Office 365 Subscription
    Glad to have helped!
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Seeker Damodar's Avatar
    Join Date
    Apr 2016
    Location
    Goa India
    Posts
    10
    Articles
    0
    Excel Version
    2013
    Much easier to understand next to flash fill
    You can use below formula
    =MID(A1,SEARCH(",",A1)+2,(SEARCH("(",A1)-1)-(SEARCH(",",A1)+2))

Tags for this Thread

Posting Permissions

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