+ Reply to Thread
Results 1 to 10 of 10

Extract multiple strings between different characters

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    54

    Exclamation Extract multiple strings between different characters

    Hi,

    I am struggling with a formula to extract certain parts of a number of long (and messy) text strings.

    I need only the bold part of the string below. The strings will be formatted similarly to the one below, however, they may include more or fewer locations and the locations may be called anything imaginable.

    The strings are located in column B, and a corresponding company name will be located in column A. Optimally, the output should be something similar to the enclosed picture.

    id=1寸ffice=Location 1兀dress=Test street兀dress2=干ip=22200勺ity=Test 1小hone=勺ountry=AA子atitude=60.1578919子ongitude=19.199143,id=2寸ffice=Away from home兀dress=Test street, 1.兀dress2=干ip=88000勺ity=Test小hone=勺ountry=UK子atitude=571.0478464子ongitude=92.9220972,id=3寸ffice=On site兀dress=Street 2兀dress2=干ip=99000勺ity=Testfielder V小hone=勺ountry=US子atitude=51.6724615子ongitude=72.5552772

    Thanks!

  2. #2
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    54

    Re: Extract multiple strings between different characters

    Please disregard the picture above and use this instead. For some reason I cannot edit the post.Attachment 642281

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    猶oft Office 365. Learning Excel all over again!!
    Posts
    28,655

    Re: Extract multiple strings between different characters

    You have attached an image. That's not very easy to work with. Also... I'm lazy. I have to re-type your information before I can begin to address your problem. That puts me off completely.

    So.

    Do yourself a favour and please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough). However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever).

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Make sure confidential info is removed first!!!!
    Glenn



  4. #4
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    195

    Re: Extract multiple strings between different characters

    Please refer to your data posted in post #.1

    To extract value as per highlighted in bold color

    1] In A1 enter :

    id=1寸ffice=Location 1兀dress=Test street兀dress2=干ip=22200勺ity=Test 1小hone=勺ountry=AA子atitude=60.1578919子ongitude=19.199143,id=2寸ffice=Away from home兀dress=Test street, 1.兀dress2=干ip=88000勺ity=Test小hone=勺ountry=UK子atitude=571.0478464子ongitude=92.9220972,id=3寸ffice=On site兀dress=Street 2兀dress2=干ip=99000勺ity=Testfielder V小hone=勺ountry=US子atitude=51.6724615子ongitude=72.5552772

    2] In B1, enter formula and copied across right until blank

    =IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A1,"=","</b><b>"),"兀dress","<r/>")&"</b></a>","//b[r][position() mod 2=1]["&COLUMN(A1)&"]"),"")

    Regards
    Bosco

  5. #5
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    54

    Re: Extract multiple strings between different characters

    Excellent Bosco, thank you!

  6. #6
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    54

    Re: Extract multiple strings between different characters

    Not sure whether to follow up on this thread or create a new, as the solution will (probably) require just a small alteration to the solution above.

    I have a similar text string;

    type=House孓ame=Test street 1a千ebt=2.600.000山ale=4.000.000己alue=1.400.000,type=Apartment孓ame=Rental apartment1千ebt=200.000山ale=1.000.000己alue=800.000,type=Timeshare孓ame=Timeshare LA千ebt=0山ale=160.000己alue=160.000

    This time, I'd like to extract the "debt" amounts rather than location. However, when I change the "兀dress" term in the formula to "山ale" and drag across the columns, I can only seem to get the values for the first and the third type of home (2.600.000 and 0). For some reason, the formula does not extract the second debt value (200.000).

    What am I missing in the formula?

    Thanks,
    Phil

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    猶oft Office 365. Learning Excel all over again!!
    Posts
    28,655

    Re: Extract multiple strings between different characters

    I don't pretend to understand Bosco's formula.... and I get the same as you!!

    =TRIM(MID(SUBSTITUTE(""&SUBSTITUTE(SUBSTITUTE($A2,"debt=",""),"山ale",""),"",REPT(" ",500)),500*2*COLUMNS($A:A),500))

    is an alternative. BUT. 2.600.00 is not a number. If you need to make it into a number, a bit more tweaking will be needed.

  8. #8
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    54

    Re: Extract multiple strings between different characters

    Thanks Glenn, worked effortlessly with a simple VALUE wrapped around the whole thing.

    Appreciate the help!

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    猶oft Office 365. Learning Excel all over again!!
    Posts
    28,655

    Re: Extract multiple strings between different characters

    Ha! I hadn't thought if that....You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  10. #10
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    195

    Re: Extract multiple strings between different characters

    This is a different between Post #.04 and Post #.06 data layout

    The Post #.04 extract specified data in the order of 1st, 3rd, 5th, 7th.... And the Post #.06 extract specified data in the order of 1st, 2nd, 3rd, 5th, 6th...

    So, the formula in Post #.06 become :

    =IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A1,"","</b><b>"),"debt=","<r/>")&"</b></a>","//b[r]["&COLUMN(A1)&"]"),"")

    Data in A1, formula in B1 and copied across

    Regards
    Bosco

+ 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 Multiple Types Text Strings
    By mycon73 in forum Excel General
    Replies: 9
    Last Post: 05-06-2019, 01:22 AM
  2. [SOLVED] how to extract data from strings containing multiple parentheses?
    By dvess11 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2019, 07:31 PM
  3. [SOLVED] Extract Values between two Text Strings, MULTIPLE condition
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-28-2015, 01:28 AM
  4. extract multiple pieces of text between two strings
    By verbatim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2014, 04:42 PM
  5. [SOLVED] Merge multiple cells in a column to strings no more than 100 characters long
    By exceler_52 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2013, 01:49 PM
  6. Extract multiple values from long strings of text
    By Patrick791 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-05-2012, 04:17 AM
  7. Find common characters in multiple strings
    By amient2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2009, 04:54 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