+ Reply to Thread
Results 1 to 7 of 7

How to extract text from a VBA string

  1. #1
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Question How to extract text from a VBA string

    Hi All,

    Would anyone know how to extract from a VBA string variable, from between the FIRST comma+space and the FIRST closed parenthesis below?

    Please Login or Register  to view this content.
    so that the result would be...

    Please Login or Register  to view this content.

    PS: After the 1st comma, there can be from 0 to unlimited amount of commas.

    I thought this would be a simple one to figure out or find in the forums, but I've had a hard time getting the above result...

    Thanks so much,
    JMC
    Last edited by jmccoughlin; 12-19-2015 at 02:54 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to extract text from a string

    If your string is in A1 you can use this:

    =MID(A1,FIND(", ",A1)+2,FIND(")",A1)-FIND(", ",A1)-2)

    Hope this helps.

    Pete

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: How to extract text from a string

    To get the text after the first X in a string, =MID(A1, FIND("X", A1&$"X")+1, 255)

    To get the text before the first Y in a string =LEFT(A1,FIND("Y", A1&"Y")-1)

    To get the text after the first ", "

    =MID(A1, FIND(", ", A1&$", ")+1, 255)

    To get the text (from that string) before the first ")"

    =LEFT(MID(A1, FIND(", ", A1&$", ")+2, 255), FIND(")", MID(A1, FIND(", ", A1&$", ")+2, 255)&")")-1)
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Question Re: How to extract text from a string

    Is it possible to extract the text from a VBA variable string (which is currently called "originalString") instead of referencing a specific cell?

    If this is possible, can the result should go into a new string, called "result"?


    FYI: I'm writing a loop that reads in "originalString" from an external file.

    Thanks,
    JMC
    Last edited by jmccoughlin; 12-19-2015 at 03:02 PM.

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: How to extract text from a string

    If you are doing this in VBA, its much easier.


    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Question Re: How to extract text from a string

    Thank you, Mike! This works perfectly*!

    Almost: I meant to add one caveat. I'll post that caveat in my next message below.
    Last edited by jmccoughlin; 12-19-2015 at 03:17 PM. Reason: correction

  7. #7
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Question Re: How to extract text from a string

    Thank you, Mike! This works perfectly...but I forgot to add one caveat

    originalString can be "IQR (Sec 2.2, Table, Row 2, Col 2) Need to delete 2nd space between 'be' and 'provided' (Sev 4, Pri 4)"
    or it can be
    originalString can be "IQR (GLOBALCOMMENT) Need to delete 2nd space between 'be' and 'provided' (Sev 4, Pri 4)"

    If there is no comma between the 1st parenthesis, can "Result" be null/blank?

    Thank you again,
    JMC

+ 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 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
  2. [SOLVED] Find and extract text string from within another text string
    By huy_le in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2013, 03:22 PM
  3. [SOLVED] Extract text from a string of text (amend formula to include new criteria)
    By robertguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2013, 04:53 PM
  4. Find and extract text string from within another text string
    By huy_le in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2013, 09:01 PM
  5. [SOLVED] How to Extract A Number from String if Text within the String Equals XYZ
    By tyrsfury in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2012, 03:30 PM
  6. [SOLVED] extract text string when you only know the last three letters of that string
    By alison0edwards in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-12-2012, 01:20 PM
  7. Extract small string of text from larger string
    By mark_jam3s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2010, 05:36 AM

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