英文:
How to apply number or date format to columns after TEXTSPLIT in Excel
问题
我使用TEXTSPLIT来拆分由多个部分组成的文本，例如：国家、日期和值：
"USA-01/01/2023-1000"
=TEXTSPLIT(A1,"-")
不幸的是，结果是一个溢出公式（动态数组），其中日期和值都是“文本”（在文本函数之后是正常的）。这在我想要进行排序时会带来很大问题，因为排序是按文本而不是日期进行的。
是否有一种方法可以使日期和数字列被识别为数字，而无需枚举每个列以应用格式？
我当前的解决方案：
目前，我使用LET来命名TEXTSPLIT的部分，然后对每个列使用CHOOSECOLS，并将数字列乘以1，然后再使用SORT：
=LET(a,TEXTSPLIT(A1,"-"),HSTACK(CHOOSECOLS(a,1),CHOOSECOLS(a,2)*1,CHOOSECOLS(a,3)*1))
但这似乎过于复杂...尤其是在Google表格中，等效的SPLIT数组公式会立即将正确的日期和数字格式应用于每个部分。
英文:
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.
答案1
得分: 6
=LET(d,TEXTSPLIT(A1,"-"),IFERROR(d+0,d))但您需要手动为相关单元格应用任何数字/日期格式。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论