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

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

Or Using `BYROW()`

to get all the output as once, without fill down!

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

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

**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.

- 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.

- 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,";")))
```

`=MAX(TEXTSPLIT(A1,";")/1)`

where cell`A1`

refers to the cell reference where the dates are delimited using semicolon`TRIM()`

if not then special characters usually and common one,`SUBSTITUTE(A1,CHAR(160),)+0`

notto answer them. As for clarification before.`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.6more comments