+ Reply to Thread
Results 1 to 8 of 8

Find and extract text string from within another text string

  1. #1
    Forum Contributor
    Join Date
    08-22-2013
    Location
    US
    MS-Off Ver
    Mac Version 2011
    Posts
    126

    Find and extract text string from within another text string

    Hi guys,

    I have a text string that has a format like this ".....&id=xxxxxxxx&....... I want to find and grand "id=xxxxxx". Is there a formula or macro that could help me do this?

    Thanks.

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,682

    Re: Find and extract text string from within another text string

    I don't know a better way but you can try this. Assuming the text string is in A1 :
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    08-22-2013
    Location
    US
    MS-Off Ver
    Mac Version 2011
    Posts
    126

    Re: Find and extract text string from within another text string

    Hi millz,

    Thanks for your quick reply. I am sorry I did not put it right. There is a bunch of other "&"'s in the string. I think your formula would work if there was no other &.

  4. #4
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,682

    Re: Find and extract text string from within another text string

    Then maybe this:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    08-22-2013
    Location
    US
    MS-Off Ver
    Mac Version 2011
    Posts
    126

    Re: Find and extract text string from within another text string

    That works! Thanks a lot millz!

  6. #6
    Forum Contributor
    Join Date
    08-22-2013
    Location
    US
    MS-Off Ver
    Mac Version 2011
    Posts
    126

    Re: Find and extract text string from within another text string

    Hi millz,

    I know you helped me solve the problem and thank you so much for that. I am now trying to understand the logic behind your code and can't seem to make sense out of it. Could you help me explain it? Appreciate it!

    Thanks.

  7. #7
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,682

    Re: Find and extract text string from within another text string

    let's say the full string contains "ZZZZZ&id=1234567&ZZZZZ"
    Please Login or Register  to view this content.
    FIND("id=",A1,1) would return 7 because the first occurrence of "id=" starts at position 7 ZZZZZ&id=1234567&ZZZZZ

    so it becomes:
    Please Login or Register  to view this content.
    next,
    Please Login or Register  to view this content.
    FIND("&",A1,7+1) is telling Excel to find for "&" starting at position 8 (which is after the first occurrence of "id="), hence returning the position of the first "&" after "id=". This would return 17 ZZZZZ&id=1234567&ZZZZZ

    so the formula would end up with:
    Please Login or Register  to view this content.
    which also means, returns the string starting from position 7, for the length of 10 characters (17 - 7)
    Please Login or Register  to view this content.
    Results: id=1234567

    hope this explains

  8. #8
    Forum Contributor
    Join Date
    08-22-2013
    Location
    US
    MS-Off Ver
    Mac Version 2011
    Posts
    126

    Re: Find and extract text string from within another text string

    Hi millz,

    Thank you for your detailed explanation. Work has been busy and I don't have time to actually look at it yet. Really appreciate it though.

+ 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. 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
  2. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  3. [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
  4. [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
  5. 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

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