+ Reply to Thread
Results 1 to 11 of 11

Extract text between one consistent point and another inconsistent point.

  1. #1
    Registered User
    Join Date
    08-20-2019
    Location
    Ireland
    MS-Off Ver
    O365
    Posts
    1

    Extract text between one consistent point and another inconsistent point.

    Hi Everyone,

    I'm trying to extract some text from a long data dump in cell A2 in a spreadsheet. The difficulty I'm having is I have only one consistent point. From the screenshot shows a sample of the string
    1. Point number 1 shows the text "Changed Status", which is the only consistent point.
    2. I want to extract the text highlighted in Yellow
    3. The carriage return at the end of the line above the Yellow highlight text is the other point, but that's not consistent position.

    So, creating the formula to find the first occurence of "Changed Status" (marked number 1 in the image) is the easy bit, but I'd like to extract the text between Changed Status and the carriage return (marked number 2 in the image) that precedes it.

    Is that possible?

    Any help you can give will be much appreciated.

    Excel Extract.png

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Extract text between one consistent point and another inconsistent point.

    Welcome to the forum.

    Try this:

    =TRIM(MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",99)),FIND("changed state",SUBSTITUTE(A2,CHAR(10),REPT(" ",99)))-99,99))
    Last edited by AliGW; 08-20-2019 at 10:24 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Extract text between one consistent point and another inconsistent point.

    Hmm, I don't know how to sort this with formula but this VBA should do the job:


    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Into B1 (let say you've got this string in A1) put:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit: AliGW was faster with formula
    Last edited by KOKOSEK; 08-20-2019 at 10:52 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Extract text between one consistent point and another inconsistent point.

    I don't know how to sort this with formula
    Look at post #2.

  5. #5
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Extract text between one consistent point and another inconsistent point.

    I know. When I was typing / pasting post You answer was not there

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Extract text between one consistent point and another inconsistent point.

    How are you bringing this text into Excel? Every time I bring text like this into Excel, Excel uses the carriage returns to start a new row/cell. Assuming, then, that the text is in column A and each carriage return represents a new row/cell (which does not seem to be assumed by the other solutions):

    1) A VLOOKUP() function will find the "changed status" text and return the text string from that row. VLOOKUP("*changed status*",A1:A100,1,FALSE)
    2) A FIND() function will locate "changed status" within that text string and return the starting character number FIND("changed status",VLOOKUP(...))
    3) The LEFT() can then be used to return everything before that character. =LEFT(VLOOKUP(...),FIND(...)-1)

    Given the assumptions I've made, that should work. From your screenshot, I am not sure how good my assumptions are. Your screenshot looks like the text is still in a text editor rather than Excel, so my assumptions could be very poor assumptions. If the text starts in a text editor, would it be easier to do this task in the text editor (ctrl-F->"changed status"->shift-home to select to start of line -> copy to clipboard -> whatever you need to do next with the text. To automate, record a macro that performs those same or equivalent steps). I expect there are a lot of different ways to do this depending on where the text starts (in a text editor or in excel or somewhere else) and where the result should end up (in Excel or in a text editor or sent to a different app/file) and whether Excel is even the best programming language/app for handling the job.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Extract text between one consistent point and another inconsistent point.

    ... each carriage return represents a new row/cell (which does not seem to be assumed by the other solutions) ...
    Not per se, but it is being seen as a marker or delimiter in mine.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Extract text between one consistent point and another inconsistent point.

    @AliGW: Right, that's what I meant by the "other assumptions" that you and KOKOSEK made in your solutions. You assumed that the entire text string (carriage returns and all) is all in one single cell. The OP's screenshot does not help us know whose assumption is better.

  9. #9
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Extract text between one consistent point and another inconsistent point.

    I think that:

    Capture.JPG

    IMHO clearly indicates string in one cell.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Extract text between one consistent point and another inconsistent point.

    @MrShorty

    The OP's screenshot does not help us know whose assumption is better.
    Maybe, maybe not, but I concur with Kokosek - I read the OP to mean that the text is all in one cell, viz.:

    I'm trying to extract some text from a long data dump in cell A2 in a spreadsheet
    Seems pretty conclusive to me in that comment.
    Last edited by AliGW; 08-20-2019 at 12:03 PM.

  11. #11
    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,893

    Re: Extract text between one consistent point and another inconsistent point.

    I agree, it does look all to be in one cell. However...

    HappyChappy... you might be happy, but you have caused some confusion here. If the above answers don't work, make us happy chappys too and post a sample excel sheet, not a non-editable picture of one.

    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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Start new animation with same departure point as arrival point in previous slide
    By isabelle.r in forum PowerPoint Formatting & General
    Replies: 1
    Last Post: 03-21-2019, 04:01 AM
  2. Replies: 6
    Last Post: 05-28-2018, 01:39 PM
  3. Replies: 7
    Last Post: 06-09-2017, 08:54 AM
  4. [SOLVED] Extract text from a given point in a text string, when data points do not share the given
    By reedersketer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2014, 03:57 PM
  5. Maths behind rotating rectangle around start point instead of default center point
    By Stanley91 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-08-2014, 11:01 AM
  6. [SOLVED] Extract Text Elements in a Cell providing stnd Starting/Stopping point.
    By Miskondukt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-13-2014, 02:48 PM
  7. Replies: 2
    Last Post: 06-27-2011, 10:47 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1