+ Reply to Thread
Results 1 to 7 of 7

Extract portion of a text string

  1. #1
    Registered User
    Join Date
    01-04-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    24

    Extract portion of a text string

    Hi,

    I am looking to extract the words "importance 8" from the below text.

    abcd: alert #12345, start 2020-08-11 23:00:00 GMT, count 300, stop 2020-08-11 23:11:00 GMT, importance 8, parent, is not done)

    The formula should pick all the strings starting with first word "importance" and second-word "parent"

    I am using the formula =MID(A2,SEARCH("GMT,",A2)+4,SEARCH("parent",A2)-SEARCH("GMT,",A2)-5) but since the GMT word is getting repeated twice it is giving the result as " count 300, stop 2020-08-11 23:11:00 GMT, importance 8, " instead of the desired result.

    Please help me.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Extract portion of a text string

    The following extracts "importance 8". It looks for "importance" to start extracting and stops when it finds "parent". Is that what you are looking for?

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by GeoffW283; 08-16-2020 at 01:31 PM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  3. #3
    Registered User
    Join Date
    01-04-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    24

    Re: Extract portion of a text string

    Thank you for your response. In the formula -6 at the end is working fine with this example but in other ones, if there's more text after the second word "parent" the result is not showing up correctly.

    Eg: abcd: alert #12345, start 2020-08-11 23:00:00 GMT, count 300, stop 2020-08-11 23:11:00 GMT, importance 8, parent, this is not coming correctly)
    Result: importance 8, parent, this is

    Can -6 in the formula be made dynamic?

    Thanks in advance

  4. #4
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Extract portion of a text string

    Try this:

    Please Login or Register  to view this content.
    Replace ; with , depending on regional settings.
    Last edited by Haluk; 08-16-2020 at 05:11 PM.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Extract portion of a text string

    Try:
    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2," parent,"," parent"&REPT(" ","100"))," importance",REPT(" ",100)&" improtance"),100,200))
    returns

    "improtance 8, parent"
    Quang PT

  6. #6
    Registered User
    Join Date
    01-04-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    24

    Re: Extract portion of a text string

    Thanks so much!

  7. #7
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Extract portion of a text string

    You're welcome...

+ 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. [SOLVED] Extract Portion of a Text String
    By andrewc in forum Excel General
    Replies: 6
    Last Post: 07-15-2020, 11:58 AM
  2. Extract a portion from string
    By irfankundi786 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-06-2016, 01:51 PM
  3. Extract the bold portion of the text in a cell
    By hfearing in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-28-2015, 07:38 PM
  4. How to extract a portion of URL and merge it with a string
    By emymeeky in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-09-2015, 11:39 AM
  5. [SOLVED] Highlight a portion of string text (the string text is a value of a formula)
    By RaymondH in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-02-2012, 06:50 AM
  6. [SOLVED] How do I extract a portion of a string into an adjacent cell?
    By tura910 in forum Excel General
    Replies: 4
    Last Post: 03-26-2012, 08:40 AM
  7. Extract a specific portion of a text string in Excel?
    By drawkcaB in forum Excel General
    Replies: 3
    Last Post: 05-18-2010, 01:48 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