1

I have some cells in my spreadsheet that have multiple dates separated by a semicolon ;.

After some googling, I found that TEXTSPLIT is probably the best formula to break this apart, but it produces several columns of data. I'm hoping to compare the values within the cell before they are broken apart and return one value that is the greatest in the group.

Is this possible? One thing I just thought of is to have a bit of a convoluted machine that breaks out values in a separate sheet and another formula that takes the max value from those rows. Would like to avoid using multiple sheets if I can.

Here is the example: TextSplit formula example

11
  • 1
    Try this way: =MAX(TEXTSPLIT(A1,";")/1) where cell A1 refers to the cell reference where the dates are delimited using semicolon Feb 20 at 2:52
  • 1
    Hey there, could you update your post with some sample dates as text so i copy and paste it in the excel to run a test Feb 20 at 2:56
  • 1
    it seems those are formatted as text or they are having some white spaces you need to clean them based on the characters they have, since i dont see any sample data on the post, hence guessing it as white spaces then use TRIM() if not then special characters usually and common one, SUBSTITUTE(A1,CHAR(160),)+0 Feb 20 at 3:00
  • 1
    @MayukhBhattacharya Welcome to the Super User community! “You don;t show any sample so I am guessing only. Only one/two sample will be helpful.” You see where I commented “And what have you done to achieve this?” The reason questions like this are avoided/shunned is because lack of effort and lack of example. They are just begging for code without providing any effort or details. This is why I encourage you not to answer them. As for clarification before. Feb 20 at 3:20
  • 2
    @Giacomo1968 Sir I completely agree with you, but I have seen, its not just for me, but I have seen in Stackoverflow as well as in Superuser where there is no attempt by OP, still they have gotten a solution. Also If I dont answer someone else will do it. But in this post has tried using TEXTSPLIT() so i have attempted with the last piece which they needed. Pardon me if I am wrong. Feb 20 at 3:24

2 Answers 2

2

If I have understood correctly, then the following formula should work:

enter image description here


  • Formula used in cell B1

=MAX(--TEXTSPLIT(A1,"; "))

Or Using BYROW() to get all the output as once, without fill down!

enter image description here


=BYROW(A1:A3,LAMBDA(α, MAX(--TEXTSPLIT(α,"; "))))

With the existing sample data the above formula works as well, and here is a sample demonstration.

enter image description here


Explanations:

  • Using TEXTSPLIT() function: it helps to split the strings by the delimiter semi-colon with a space --> ; across columns. (Please refer the .gif it shows).

Since its a function that falls under the function library Text hence one can realize that when used it will split the strings of dates as text, which creates a problem for future data manipulations, (Note: In Excel dates and times are stored as numbers, therefore the integer part represents the dates while the decimal is the time for the date.)

Hence as mentioned in OP by user using DATEVALUE() it can be converted to true dates or using double unary or by adding 0 or by multiplying by 1 or diving by 1, which eventually converts.


  • Once the above is done, then we can wrap it within MAX() function to return latest/greatest/max/highest (whichever suits in saying) date.

Reasons:

Why User was getting 0 while using the formula provided by me, while they were getting the respective output using DATEVALUE(), to explain, the screenshot clarifies it.

enter image description here


  • User was using delimiter only semicolon ; while I used semicolon and a space ;
  • So when when wrapping within MAX() with the double unary it was returning an error, as it was only converting the first one to a number while the second one was returning an error as it had a leading space. But the one with delimiter semicolon and space should work, refer screenshots.

enter image description here


  • Using DATEVALUE() function it takes care of the leading space and converts the same to a number and lastly wrapping within MAX() gives the desired output. Alternative ways, are has been mentioned in the beginning using ; , and another way to deal with it is using TRIM()

=MAX(--TRIM(TEXTSPLIT(A3,";")))

5
  • 1
    Thank you. What is the -- for in front of the TEXTSPLIT formula? Feb 20 at 3:18
  • 2
    It means -- double unary it converts numbers formatted as text to actual/true numbers that excel understand, in excel date/time are stored as numbers, so using -- or +0 or /1 or *1 it converts text formatted dates to true dates. Feb 20 at 3:19
  • 1
    @MayukhBhattacharya You seem very smart and skilled. Do not fall into the trap of answering questions that show little to effort and don’t even show examples of data. You are falling into a trap. Maybe this answer will help the original poster, but in general you should ask for clarification and samples of data before you post any answer. This is for everyone’s good especially you, the person spending precious time to post an answer. Feb 20 at 3:24
  • 1
    @Giacomo1968 Sir completely agree with you. Feb 20 at 3:25
  • 1
    @MayukhBhattacharya I think you can back out of this specific question safely. But please be careful when answering questions in the future: They should show effort or (at least) show samples. Remember, your time and efforts have value. You answers are very good here; just be careful when you answer. Feb 20 at 3:29
1

I have not tried the answer provided by @MayukhBhattacharya, but I do appreciate the information provided, so I gave the answer to that user.

I determined that the answers needed to be changed to a DateValue, so I inserted the DATEVALUE formula between the MAX and TEXTSPLIT formulas.

The formula I used was:

=MAX(DATEVALUE(TEXTSLIT(A2, ";")))

See example below:

Incert DATEVALUE into formula

Be sure to format the cell using the short date field.

4
  • 1
    DATEVALUE is not required, for any user around world using Excel for every dates/times are stored as numbers in Excel. The Integer part is the date while the decimal part is the time. So, when you are using text functions like TEXTSPLIT() to split, it formats the dates as text, to convert back them to numbers, you can use DATEVALUE() or a shorter way is of using -- double unary or the other ways as explained in earlier comments. and then wrap within the aggregate functions like MIN() or MAX() to get desired output. To make it shorter i have used double unary Feb 20 at 3:46
  • 1
    Thank you for clarifying. I posted the answer because this is how I came to resolve this issue. It's probably not as slick nor is it using best practices which I'm sure yours is, but thought it might be a good/simple answer for beginners like myself. Thank you again for your feedback! Feb 20 at 3:48
  • 1
    Ok, I have realized one thing, the DATEVALUE() helps in converting the text into actul dates here while double unary did for the first instance, this is because in your formula the delimiter you have used is ; only semicolon, while i have used ; semicolon and the following space it carries. Also wrapping TRIM() would remove the leading space. =MAX(--TRIM(TEXTSPLIT(A2,";"))) Feb 20 at 4:28
  • 1
    Very cool, thank you for the clarification @Mayukh Bhattacharya Feb 20 at 5:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .