How to apply number or date format to columns after TEXTSPLIT in Excel

I use TEXTSPLIT to cut text composed of multiple parts, like for example: the country, the date and the value:



Unfortunately, the result is a spill formula (dynamic array) where the date and the value are "text" (which is normal after a text function). This poses a big problem if I want to SORT, because the sorting is applied as TEXT, not as date.

Is there a way to have the date and number columns recognized as number, without having to enumerate each column to apply the format?

My current solution:

For now I use let to name the textsplit part and I use choosecols for each column and multiply by 1 the number ones… before using SORT:


But this seems overcomplicated… especially when the equivalent SPLIT array formula in GoogleSheet applies immediately the correct dates and number format to each part.

>Solution :

You could use:


but you’ll have to manually apply any number/date formatting to the relevant cells.

Leave a Reply