+ Reply to Thread
Results 1 to 10 of 10

Extract multiple strings between different characters

  1. 09-20-2019, 10:19 AM #1
    Phil123456789
    Phil123456789 is offline
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    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¤office=Location 1¤adress=Test street¤adress2=¤zip=22200¤city=Test 1¤phone=¤country=AA¤latitude=60.1578919¤longitude=19.199143,id=2¤office=Away from home¤adress=Test street, 1.¤adress2=¤zip=88000¤city=Test¤phone=¤country=UK¤latitude=571.0478464¤longitude=92.9220972,id=3¤office=On site¤adress=Street 2¤adress2=¤zip=99000¤city=Testfielder V¤phone=¤country=US¤latitude=51.6724615¤longitude=72.5552772

    Thanks!
    Reply With QuoteRegister To Reply
  2. 09-20-2019, 10:22 AM #2
    Phil123456789
    Phil123456789 is offline
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    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
    Reply With QuoteRegister To Reply
  3. 09-20-2019, 10:40 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    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



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 09-20-2019, 12:30 PM #4
    Bosco
    Bosco is offline
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    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¤office=Location 1¤adress=Test street¤adress2=¤zip=22200¤city=Test 1¤phone=¤country=AA¤latitude=60.1578919¤longitude=19.199143,id=2¤office=Away from home¤adress=Test street, 1.¤adress2=¤zip=88000¤city=Test¤phone=¤country=UK¤latitude=571.0478464¤longitude=92.9220972,id=3¤office=On site¤adress=Street 2¤adress2=¤zip=99000¤city=Testfielder V¤phone=¤country=US¤latitude=51.6724615¤longitude=72.5552772

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

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

    Regards
    Bosco
    Reply With QuoteRegister To Reply
  5. 09-23-2019, 06:17 AM #5
    Phil123456789
    Phil123456789 is offline
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Extract multiple strings between different characters

    Excellent Bosco, thank you!
    Reply With QuoteRegister To Reply
  6. 10-10-2019, 03:47 AM #6
    Phil123456789
    Phil123456789 is offline
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    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¤name=Test street 1a¤debt=2.600.000¤sale=4.000.000¤value=1.400.000,type=Apartment¤name=Rental apartment1¤debt=200.000¤sale=1.000.000¤value=800.000,type=Timeshare¤name=Timeshare LA¤debt=0¤sale=160.000¤value=160.000

    This time, I'd like to extract the "debt" amounts rather than location. However, when I change the "¤adress" term in the formula to "¤sale" 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
    Reply With QuoteRegister To Reply
  7. 10-10-2019, 04:34 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    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=","¦"),"¤sale","¦"),"¦",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.
    Reply With QuoteRegister To Reply
  8. 10-10-2019, 06:32 AM #8
    Phil123456789
    Phil123456789 is offline
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Extract multiple strings between different characters

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

    Appreciate the help!
    Reply With QuoteRegister To Reply
  9. 10-10-2019, 06:44 AM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    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.
    Reply With QuoteRegister To Reply
  10. 10-10-2019, 07:26 AM #10
    Bosco
    Bosco is offline
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1