+ Reply to Thread
Results 1 to 12 of 12

Find where a text string changes compared to another similar text string

  1. 10-10-2022, 10:04 AM #1
    Graxon
    Graxon is offline
    Registered User
    Join Date
    07-11-2019
    Location
    London, England
    MS-Off Ver
    Excel 365
    Posts
    18

    Find where a text string changes compared to another similar text string

    Hi,

    I have some really long and annoying XPath data and need to figure out how many characters there are in the string until it changes compared to the XPath cell above. To illustrate, the date I have looks like this:

    /html/body/div[4]/div/div[5]/div[1]/div[4]/ul/li[2]/a/div/div[2]/div[1]/div[1]
    /html/body/div[4]/div/div[5]/div[1]/div[4]/ul/li[3]/a/div/div[2]/div[1]/div[1]
    ...etc...

    I need a formula that tells me that the changing character in this patterned string is character number 49 (counted from the left, I guess by using the LEFT function).
    If this is possible, a bonus would be to also know how many characters there are from the changing character to the last (I guess by using the RIGHT function).

    Thanks in advance!
    Reply With QuoteRegister To Reply
  2. 10-10-2022, 10:35 AM #2
    tanasedn
    tanasedn is offline
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Find where a text string changes compared to another similar text string

    Hi,
    Do you have the function SEQUENCE?
    Diana Tanase

    If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
    Reply With QuoteRegister To Reply
  3. 10-10-2022, 10:47 AM #3
    tanasedn
    tanasedn is offline
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Find where a text string changes compared to another similar text string

    Hi,
    Assuming that the two texts are in A1 and A2, respectively, to extract the character from A2 that is different from the one from A1, use:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    and for the text after that char:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-10-2022, 11:10 AM #4
    AliGW
    AliGW is offline
    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
    80,830

    Re: Find where a text string changes compared to another similar text string

    SEQUENCE is in MS365 and Excel 2021, not Excel 2016.
    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.
    Reply With QuoteRegister To Reply
  5. 10-10-2022, 11:27 AM #5
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,053

    Re: Find where a text string changes compared to another similar text string

    I make it 50... not 49...


    =AGGREGATE(15,6,ROW(INDIRECT("1:"&LEN(A3)))/(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)=MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)=FALSE),1)
    Attached Files Attached Files
    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
  6. 10-10-2022, 11:29 AM #6
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,053

    Re: Find where a text string changes compared to another similar text string

    On second thoughts. No need to use the volatile INDIRECT function:

    =AGGREGATE(15,6,ROW($1:$1000)/(MID(A2,ROW($1:$1000),1)=MID(A3,ROW($1:$1000),1)=FALSE),1)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-10-2022, 11:32 AM #7
    tanasedn
    tanasedn is offline
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Find where a text string changes compared to another similar text string

    Thanks, Ali!
    Without SEQUENCE function, to extract the char that's different in A2 vs A1:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    And for the remaining text:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 10-11-2022, 09:17 AM #8
    Graxon
    Graxon is offline
    Registered User
    Join Date
    07-11-2019
    Location
    London, England
    MS-Off Ver
    Excel 365
    Posts
    18

    Re: Find where a text string changes compared to another similar text string

    Quote Originally Posted by Glenn Kennedy View Post
    On second thoughts. No need to use the volatile INDIRECT function:

    =AGGREGATE(15,6,ROW($1:$1000)/(MID(A2,ROW($1:$1000),1)=MID(A3,ROW($1:$1000),1)=FALSE),1)
    This is exactly what I was looking for, thank you!

    Just out of curiosity: I was messing around and tried to apply your formula without the AGGREGATE function, and rather using the SMALL function. However, I'm getting an error and I don't understand why. Is it even possible to re-write it without AGGREGATE?
    Reply With QuoteRegister To Reply
  9. 10-11-2022, 09:36 AM #9
    tanasedn
    tanasedn is offline
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Find where a text string changes compared to another similar text string

    This one, maybe:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 01-22-2023, 03:43 AM #10
    Graxon
    Graxon is offline
    Registered User
    Join Date
    07-11-2019
    Location
    London, England
    MS-Off Ver
    Excel 365
    Posts
    18

    Re: Find where a text string changes compared to another similar text string

    Quote Originally Posted by tanasedn View Post
    Hi,
    Do you have the function SEQUENCE?
    Bumbing an old (and solved) thread here, but as it happens I now have access to a newer Excel that has the SEQUENCE function! I have little knowledge and no experience in using the function. How would you proceed to solve the OP-problem with SEQUENCE?
    Reply With QuoteRegister To Reply
  11. 01-22-2023, 04:54 AM #11
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,053

    Re: Find where a text string changes compared to another similar text string

    Hi. Before we get going on this one...

    Is your forum profile showing the Excel PRODUCT that you need this request to work with?

    The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

    The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

    Cheers,

    Glenn.
    Reply With QuoteRegister To Reply
  12. 01-22-2023, 05:03 AM #12
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,053

    Re: Find where a text string changes compared to another similar text string

    It's still th 50th character... and can also be returned using:

    =LET(A,SEQUENCE(LEN(A2)),B,SEQUENCE(LEN(A3)),FILTER(A,MID(A2,A,1)<>MID(A3,B,1)))
    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] Find partial text string within another text string return the full value
    By andreaspid in forum Excel General
    Replies: 5
    Last Post: 07-10-2017, 10:36 AM
  2. Find today's date in a text string in a column and return this string and....
    By Neyme in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-30-2015, 03:20 PM
  3. Is it possible to find out if any part of a text string appears in another text string?
    By Lmel0611 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-18-2015, 07:30 PM
  4. Excel for DNA Sequencing analysis - how can I find a string of text within a large string?
    By ledzep618 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-11-2015, 10:41 PM
  5. [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
  6. Using a partial text string compared to a column with VLOOKUP
    By carolyn.brussee in forum Excel General
    Replies: 6
    Last Post: 11-18-2010, 12:39 PM
  7. find text similar to yyyy-mm-dd in string
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2006, 05:30 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