excelexcel-formuladynamic-arrayscell-formatting

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:

"USA-01/01/2023-1000"

=TEXTSPLIT(A1,"-")

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:

=LET(a,TEXTSPLIT(A1,"-"),HSTACK(CHOOSECOLS(a,1),CHOOSECOLS(a,2)*1,CHOOSECOLS(a,3)*1))

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:

    =LET(d,TEXTSPLIT(A1,"-"),IFERROR(d+0,d))

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