+ Reply to Thread
Results 1 to 8 of 8

VBA code to extract strings

  1. #1
    Forum Contributor
    Join Date
    10-10-2015
    Location
    the Dysfunctional Empire of Bulgaria
    MS-Off Ver
    2010
    Posts
    102

    VBA code to extract strings

    I am trying to extract a two or three letter string from another string.

    In one column (A) I have a list of two or three letter strings.

    In the adjacent column (B) I have a combination of the known string and the target string.

    The value in column B is in the format STRING$STRING. All strings are either a two or three letter combination. The two strings will never be the same. The $ sign is a unique character.

    In my mind the VBA code will work by first finding the location of the $ sign. Next the code will look to the left of the $ and make a note of the two or three letter string, then the code will repeat the same process but to the right of the string. Next the code will compare the two strings to the string in column A, and the unique string will be recorded in column B.

    Example 1:
    Column A: SD
    Column B: SD$HYT
    New Column B: HYT

    Example 2:
    Column A: HYT
    Column B: HVY$HYT
    New Column B: HVY

    The difficulty I am having regards identifying how many characters my column B strings are.

    The cell begins with a string, followed by the $ sign, followed by the second string, followed by a space, followed by a varying amount of text.

    Given that both strings are always situated between the beginning of the cell and the $ sign, and the $ sign and a space, I just need to be able to extract the letters characters between the mentioned “boundaries.”

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: VBA code to extract strings

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    10-10-2015
    Location
    the Dysfunctional Empire of Bulgaria
    MS-Off Ver
    2010
    Posts
    102

    Re: VBA code to extract strings

    Thank you. That was fast.

    How do I get rid of the text right of the space?

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: VBA code to extract strings

    I have an answer, I'm just struggling to post it at the moment. Forum problems

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: VBA code to extract strings

    Try this. It assumes your data is in columns A and B and starts in row 2. Adjust the rData range as necessary.

    Please Login or Register  to view this content.

    Regards , TMS

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: VBA code to extract strings

    Sorry, forgot to say, you need to change all the square brackets ([]) to round brackets (()).

    It was the only way that I could get the code to post.

    Regards, TMS

  7. #7
    Forum Contributor
    Join Date
    10-10-2015
    Location
    the Dysfunctional Empire of Bulgaria
    MS-Off Ver
    2010
    Posts
    102

    Re: VBA code to extract strings

    I stumbled my way into the following code which seems to accomplish everything I need.

    Please Login or Register  to view this content.
    The code TMS posted is in line one, however it was only removing the string where $ was the LAST symbol.

    I added line two which removes the string in cases where $ is the FIRST symbol.

    I still cannot exactly explain to myself why line one has "", and line two has " ".

    Line three removes the additional text by locating the blank space, and taking the first X number of characters minus one using the left function.

    If anyone can explain to me the ""/" " issue that would be really helpful.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: VBA code to extract strings

    The attached workbook has various examples. The original code and also adapted for columns 4 and 6.

    Please note, it is always better to provide a sample workbook that reflects the actual data rather than provide a hypothetical description.
    Attached Files Attached Files

+ 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] How to extract strings from hyphen separated strings
    By sharathnarayanan in forum Excel General
    Replies: 5
    Last Post: 11-22-2014, 10:01 AM
  2. [SOLVED] Extract strings from first dot find..???
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-18-2013, 11:16 PM
  3. Extract text strings
    By wazing in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-22-2010, 01:25 PM
  4. extract text strings
    By jhelliar in forum Excel General
    Replies: 5
    Last Post: 09-16-2008, 12:19 PM
  5. extract numbers out of strings.
    By mikeburg in forum Excel General
    Replies: 3
    Last Post: 08-06-2008, 04:52 PM
  6. [SOLVED] HOW TO EXTRACT STRINGS FROM CELLS
    By vidhya in forum Excel General
    Replies: 2
    Last Post: 11-17-2005, 08:45 AM
  7. [SOLVED] Extract numbers from strings
    By Stan Altshuller in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-18-2005, 10:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1