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.