+ Reply to Thread
Results 1 to 8 of 8

Remove substring with RegEx

  1. #1
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Remove substring with RegEx

    Hello, I have a problem that I think needs RegEx to solve and I am confounded by RegEx

    I have two Identical columns A & B were some of the cell values are in th form

    TextString - DifferentTextString [string]? e.g Baltimore Asthma Management - How strongly do you value your relationship with [Field-2]?

    In column A I need to remove the middle part - DifferentTextString and the ? leaving TextString [string] as the cell value e.g Baltimore Asthma Management [Field-2]

    And in column B I need only the middle part DifferentTextString e.g How strongly do you value your relationship with

    Thanks in advance for any help on this
    Attached Files Attached Files
    Last edited by capson; 02-09-2018 at 10:37 AM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Remove substring with RegEx

    Try this on column A.
    Please Login or Register  to view this content.
    As for column B...
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Remove substring with RegEx

    CK76, thanks, that's almost perfect

    For Sub DemoA I get TextString -[string] but need TextString [string]

    How to alter the code to do this?

    Thans

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Remove substring with RegEx

    Just change.
    From...
    Please Login or Register  to view this content.
    To...
    Please Login or Register  to view this content.
    And use Submatches(0) instead of 1.

    Or use second replace, to replace "-" with "".
    Last edited by CK76; 02-09-2018 at 12:28 PM. Reason: Edit: Typo removed extra [ from code.

  5. #5
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Remove substring with RegEx

    Wonderful, thanks again
    Last edited by capson; 02-09-2018 at 12:29 PM.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,999

    Re: Remove substring with RegEx

    Quote Originally Posted by capson View Post
    TextString - DifferentTextString [string]? e.g Baltimore Asthma Management - How strongly do you value your relationship with [Field-2]?

    In column A I need to remove the middle part - DifferentTextString and the ? leaving TextString [string] as the cell value e.g Baltimore Asthma Management [Field-2]

    And in column B I need only the middle part DifferentTextString e.g How strongly do you value your relationship with
    2 in 1
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Remove substring with RegEx

    Thank you Jindon, that worked great

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,999

    Re: Remove substring with RegEx

    You are welcome and thanks for the rep.

+ 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. Use More then one Regex to determine a a name
    By AbertBean in forum Excel General
    Replies: 10
    Last Post: 02-02-2018, 08:55 AM
  2. How to search substring against row and if duplicates found, remove them?
    By joshman10888 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-03-2016, 10:00 PM
  3. Macro to search cells for substring and replace contents of cell if substring is found
    By robbyvegas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2015, 06:40 AM
  4. macro to copy cell contents then remove substring of contents in new cell - Help
    By three_jeeps in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2015, 01:19 AM
  5. Search substring within range, report the substring found
    By Brooke1578 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-20-2012, 03:56 PM
  6. Using regex in vba to remove emty xml tags - is it possible?
    By Laurentius77 in forum Excel General
    Replies: 0
    Last Post: 06-23-2012, 03:10 AM
  7. [SOLVED] Extract Substring, then Ignore that Substring, while collecting data from Other substrings
    By Sameki121 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-17-2012, 05:21 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1