I have a set of data with this format: Each cell has one or some names, each of them are followed by a date. I want to compare the dates which are presented in each cell and check whether they are the same or not.

Example of a cell content: university XXX (2016-10-21) company YYY (2016-10-22)

I used the formula: =MID(A1,SEARCH("(",A1,1) 1,10) to find the first date. how could I find 2nd, 3rd, ... dates?

Thank you in advance,

CodePudding user response：

If you have Office 365 (Windows), here's one way that should work, depending on the variability in your real data:

=LET(arr,--MID(FILTERXML("<t><s>" & SUBSTITUTE(A1," ","</s><s>") & "</s></t>","//s[contains(.,'(')]"),2,10),
         numDates,COUNT(arr),
         AGGREGATE(14,6,arr,SEQUENCE(numDates)))

• create an XML
• Use FILTERXML to return only nodes containing the (
• Convert those nodes to a date serial number
  • Will => error if the 10 characters subsequent to the ( are not numeric
• Return those values.
• Then you can compare those values however you want

For example, if you wanted to see if all the dates were the same:

=COUNT(UNIQUE(
      LET(arr,--MID(FILTERXML("<t><s>" & SUBSTITUTE(A1," ","</s><s>") & "</s></t>","//s[contains(.,'(')]"),2,10),
         numDates,COUNT(arr),
         AGGREGATE(14,6,arr,SEQUENCE(numDates)))))=1

If you don't have Office 365, or if your data is more varied than what you show, such that the method I used for determining if the parenthesized values are dates is not reliable, I suggest you develop a VBA solution, possibly using Regular Expressions.

CodePudding user response：

Easiest if done step by step:
So break down your MID(A1,SEARCH("(",A1,1) 1,10) (and make it more specific to dates - you don't want to match "(KACST)") as:
B1: =SEARCH("(2",A1,1) and H1: =mid(A1,B1 1,10)
Then add
C1: =SEARCH("(2",A1,B1 1) This tells the search to start from the character after the one it has already found
I1: =mid(A1,C1 1,10)
etc