+ Reply to Thread
Results 1 to 10 of 10

Extract multiple text strings from one long string in a cell, place results in another

  1. 03-09-2015, 01:33 AM #1
    o0cerberus0o
    o0cerberus0o is offline
    Registered User
    Join Date
    03-08-2015
    Location
    Georgia, USA
    MS-Off Ver
    2013
    Posts
    3

    Extract multiple text strings from one long string in a cell, place results in another

    I have a tried different ways to extract a string from a cell with one long string, and place the results in another cell separated with one space, yet can't quite get the desired results.
    Here is what I have and what I've tried (the closest one to work).

    Contents of the cell is a generated text so that the last part should always remain constant, for example the text I need is always after "K7." yet before ":" except for the last text string, and the texts vary in length.Easy enough?!

    Generated string

    //somewhere.com/ll=24.42648802570747,-82.63483959401013&chart=89&zoom=
    5&plan=A.K7.KSEF:F.K7.RINSE:V.K7.LBV:F.K7.TIRTE:F.K7.SWAGS:F.K7.DEEDS:N.K7.MTH:A.K7.7FA1

    Formulas so far are

    =TRIM(MID(RIGHT(A1,FIND("7.",A1&"[")-6),FIND(":",A1&"7.")+1,99))
    resulting in
    KSEF:F.K7.RINSE:V.K7.LBV:F.K7.TIRTE:F.K7.SWAGS:F.K7.DEEDS:N.K7.MTH:A.K7.7FA1

    or
    =MID(A1,FIND("7.",A1,FIND(":",A1)+1)+1,255)
    resulting in
    .KSEF:F.K7.RINSE:V.K7.LBV:F.K7.TIRTE:F.K7.SWAGS:F.K7.DEEDS:N.K7.MTH:A.K7.7FA1

    I'm admittedly stumped as to how to complete these formulas to break out just the texts.
    Have even considered if a VB script is in order, just can't recall my old lessons.

    Ultimately it would be nice to create a little app that would do these functions just by pasting the string in, have results out.

    Thanks in advanced for any input (pun intended) or advice.
    Reply With QuoteRegister To Reply
  2. 03-09-2015, 03:18 AM #2
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Extract multiple text strings from one long string in a cell, place results in another

    i think you lack some explanation.
    what are the result expected
    maybe additional sample data with expected result could help members analyze your proble.

    To attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Regards and welcome to the forum.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  3. 03-09-2015, 03:41 AM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Extract multiple text strings from one long string in a cell, place results in another

    Try this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 03-09-2015, 03:20 PM #4
    o0cerberus0o
    o0cerberus0o is offline
    Registered User
    Join Date
    03-08-2015
    Location
    Georgia, USA
    MS-Off Ver
    2013
    Posts
    3

    Re: Extract multiple text strings from one long string in a cell, place results in another

    Sorry for that, it was 2am here. Attached is a sample file.

    vlady: The "results" I am looking for are the way-point codes along a given flight path. The hyperlink would give you the whole plan as a graphic map, but in order for me to use these codes in a flight computer, I just need to have the strings(way-point codes) to use. You may can see how trying the copy and paste could take awhile if you had many way-points. Thus the call for the extraction.

    FlameRetired, That formula creates an error, I think, due to the colon right after the http

    Maybe the enclosed file can shed some light.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-09-2015, 05:28 PM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Extract multiple text strings from one long string in a cell, place results in another

    The attachment did help. I cheated a bit and started the search at character position 10. It works for the data supplied. Try it on the rest. I put this in A9 and filled down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    I got this.

    Row\Col
    A
    9
    		 KSEF
    10
    		 KSRQ
    11
    		 KATL
    12
    13
    		 RINSE
    14
    		 KVNC
    15
    		 SPA
    16


    Are there items this won't work on?
    Last edited by FlameRetired; 03-09-2015 at 05:34 PM.
    Reply With QuoteRegister To Reply
  6. 03-09-2015, 05:40 PM #6
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract multiple text strings from one long string in a cell, place results in another

    Use this formula in B1 and pull it to the right and down until you see blanks to get your results

    =TRIM(RIGHT(SUBSTITUTE("."&TRIM(MID(SUBSTITUTE(":"&REPLACE($A1,1,FIND("A.K7.",$A1)+4,""),":",REPT(" ",255)),255*COLUMNS($A:A),255)),".",REPT(" ",255)),255))

    Please see attached file
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  7. 03-09-2015, 05:43 PM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Extract multiple text strings from one long string in a cell, place results in another

    Sorry o0cerberus0o; I didn't read the strings thoroughly enough.
    Reply With QuoteRegister To Reply
  8. 03-09-2015, 09:32 PM #8
    o0cerberus0o
    o0cerberus0o is offline
    Registered User
    Join Date
    03-08-2015
    Location
    Georgia, USA
    MS-Off Ver
    2013
    Posts
    3

    Re: Extract multiple text strings from one long string in a cell, place results in another

    Thanks for the help! Brilliant work! AlKey-I think the substitute, repeat functions are what I could not grasp. FlameRetired- All advice is welcome, your formula gave me an idea for something else I'm tying together! This gets me started with the correct data, to complete the pertinent details.
    Now onto the next step! Altitudes and fuel!
    20150309_3.jpg
    Reply With QuoteRegister To Reply
  9. 03-09-2015, 09:55 PM #9
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract multiple text strings from one long string in a cell, place results in another

    Glad that you found formula useful Thanks for the feedback!
    Reply With QuoteRegister To Reply
  10. 03-09-2015, 10:15 PM #10
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Extract multiple text strings from one long string in a cell, place results in another

    Yes. Thanks for the feedback and the rep!
    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. what formula to extract text from a long string?
    By SEMMatt in forum Excel General
    Replies: 5
    Last Post: 10-07-2012, 10:23 PM
  2. 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
  3. [SOLVED] Extract strings from a longer text string
    By Frostyvegi in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-26-2012, 01:44 AM
  4. Extract text string from cell based on conditions and paste results in another cell.
    By fodio in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2012, 07:54 AM
  5. [SOLVED] Extract specific value from a long text string
    By Dinesh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2006, 11:30 PM

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