The formula
=SPLIT("1\10/10\00002.2";"\")
doesn't result equal to
=MAP(SPLIT("1\10/10\00002.2";"\"); LAMBDA(v; v))
At the former, implicity conversion is inhibited; the latter, it takes place.
Why? Is there a way to control this?
-
1"At the former, implicity conversion is inhibited; " This looks to be wrong for me. What is the locale used in your spreadsheet? What is the result that you are getting (include values and data types).Wicket– Wicket2025-11-15 19:00:49 +00:00Commented Nov 15 at 19:00
-
It remains unclear what you're actually trying to do. Please edit the question and show the desired result, including data types.doubleunary– doubleunary2025-11-15 21:30:03 +00:00Commented Nov 15 at 21:30
Add a comment
|
2 Answers
To ensure that the result of the formula returns each part as text, use TO_TEXT:
=ArrayFormula(MAP(TO_TEXT(SPLIT("1\10/10\00002.2";"\")), LAMBDA(v; v)))
This will return
| A | B | C |
|---|---|---|
| 1 | 10/10 | 2.2 |
I have tested the above using United States, Uruguay and France as the spreadsheet locale.
Alternative
If you need to get 0002.2 instead of 2.2, add a ' before 00002.2. Depending on your specific use case, the most straightforward approach might be to add it manually or use a formula.
Formula example:
=MAP(SPLIT(SUBSTITUTE("\" &"1\10/10\00002.2", "\", "\'"),"\"), LAMBDA(v,v))
| A | B | C |
|---|---|---|
| 1 | 10/10 | 00002.2 |
4 Comments
Daniel Bandeira
You've hit the point! In this case, map could be used to keep things cleaner.
doubleunary
Note that the formula in this answer returns
{ "1", "10/10", "2.2" } instead of { "1", "10/10", "00002.2" }.
bricks96
Wouldn't the MAP be redundant?
=ArrayFormula(TO_TEXT(SPLIT("1\10/10\00002.2";"\"))) produces the same output.
Wicket
I ncluded it because the OP included it in the question
lambda(v, v) is an identity function, but it may not return the value in its original number format.
The two formulas in the question get the same result, but in different number formats. split() coerces 10/10 to the date 10/10/2025, and the map() wrapper shows that value as a dateserial.
If you want to get the three values as text, use regexextract(), like this:
=regexextract("1\10/10\00002.2", "(.+)\\(.+)\\(.+)")
This gets the three values as text in the same format they appear in the string:
{ "1", "10/10", "00002.2" }
...instead of using to_text(split()) that would coerce the values to numbers and dates first, and then converting to text, which would get this:
{ "1", "10/10", "2.2" }
See regexextract() and Working with date and time values in Google Sheets.