+ Reply to Thread
Results 1 to 8 of 8

Extract text following the "_" sign and all text to the LEFT

Hybrid View

  1. 07-27-2018, 04:25 AM #1
    abcdeabdce
    abcdeabdce is offline
    Registered User
    Join Date
    10-01-2017
    Location
    USA
    MS-Off Ver
    10
    Posts
    46

    Extract text following the "_" sign and all text to the LEFT

    Hi Folks -

    I have a need to search a column and find a "_" symbol. Then, extract the text immediately following that as well as all text to the LEFT of that.

    I tried to use spaces as delimiated but it varies. Any slick formulas to get that?

    Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-27-2018, 04:32 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Extract text following the "_" sign and all text to the LEFT

    Hi htere. use this:

    =TRIM(LEFT(A4,SEARCH(" ",A4,SEARCH("_",A4))))
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 07-27-2018, 04:35 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,767

    Re: Extract text following the "_" sign and all text to the LEFT

    This works for your sample data:

    =TRIM(LEFT(SUBSTITUTE(LEFT(A4,FIND("_",A4)+98)," ",REPT(" ",99)),297))
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 07-27-2018, 04:45 AM #4
    abcdeabdce
    abcdeabdce is offline
    Registered User
    Join Date
    10-01-2017
    Location
    USA
    MS-Off Ver
    10
    Posts
    46

    Re: Extract text following the "_" sign and all text to the LEFT

    Thank you both! Each solution works like a charm!!!!

    Have a great day!!!
    Reply With QuoteRegister To Reply
  5. 07-27-2018, 04:47 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Extract text following the "_" sign and all text to the LEFT

    You're welcome!
    Reply With QuoteRegister To Reply
  6. 07-27-2018, 05:01 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,767

    Re: Extract text following the "_" sign and all text to the LEFT

    Ooh, nice, Glenn!!!
    Reply With QuoteRegister To Reply
  7. 07-27-2018, 05:03 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,767

    Re: Extract text following the "_" sign and all text to the LEFT

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  8. 09-27-2018, 03:37 PM #8
    abcdeabdce
    abcdeabdce is offline
    Registered User
    Join Date
    10-01-2017
    Location
    USA
    MS-Off Ver
    10
    Posts
    46

    Re: Extract text following the "_" sign and all text to the LEFT

    .............
    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. [SOLVED] VBA Code to extract Text btwn ">TEXT<" ?
    By mave27 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-04-2015, 09:01 AM
  2. [SOLVED] EXTRACT TEXT "SHM" or "SHGB"
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-29-2014, 07:57 AM
  3. [SOLVED] Expanded and collapsed the rows by toggle "+" or "-" sign on left of the text
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-24-2013, 02:49 PM
  4. Trying to extract "Text" inbetween ":" and ","
    By Paulzr27 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-06-2013, 05:48 AM
  5. Vlookup LEFT of the "^" sign, IF "^" is present in the string
    By ConstantinV in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-02-2013, 03:26 PM
  6. Extract value from rows when top row text = "Admin" and "Clin", then calculate the mean
    By jidef in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-22-2012, 09:38 AM
  7. "Left" function - How to skip over a full stop in text
    By Grimace in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-20-2010, 03:08 AM

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