1

=index(split("A (2)"," "),2) renders as -2 even when cell number format is set to "Plain text". It also breaks further functions like REGEXREPLACE that expects text, not a number.

This thread discusses similar problem, but =index(arrayformula(To_Text(split("A (2)"," "))),2) also renders -2. It also suggests to temporarily replace - with |, but that feels hacky, unreliable and non-future-proof.

How do I do text processing using Google Sheets' formulas without surprises, without the weak types feeling?

1
  • You could use REGEXEXTRACT("A (2)","\(\d\)$" to pull it out. REGEXEXTRACT seems to always return a string. But I'm assuming that you are pertaining specificlaly to SPLIT?
    – David Tan
    Nov 17, 2022 at 13:53

3 Answers 3

0

What do you think about this? :

=index(split("A (2)","()"),2)
0
=TEXT(INDEX(SPLIT("m (2)", " "),2),"0;(0)")

This does unfortunately convert ALL negative values to (number) format, so if that breaks anything, then maybe you'll be more interested in a RegEx solution?

=REGEXEXTRACT("A (2)","\(\d\)$")

This one might be too specific though. Firstly, it doesn't give the same flexibility since it doesn't use INDEX and also because it expects the pattern to be at the end.

Maybe the solution would depend heavily on the expected data structure and expected results.

1
  • Just like with the replacement of - with |, REGEXEXTRACT would probably work, but would also be a workaround - porting a code which expects to split and rejoin strings and lists to be round-trippable to Google Sheets formula may require a fundamental redesign. Or can REGEXEXTRACT also return array (without SPLIT's auto-conversion)? I already solved the task by using slightly different approach. The question remains of how to do it reliably and without the loss of genericity.
    – Vi0
    Nov 18, 2022 at 19:47
0

Negative numbers are often expressed as (2) in accounting. If you manually enter that value in a cell by itself, it will automatically turn to -2. Likewise, when you enter Nov 2022 in a cell, the value becomes 11/1/2022 and it is automatically formatted as MMM yyyy, displaying as Nov 2022.

The split() function will automatically do the same kind of conversion for many types of text strings and store them as numeric values.

To avoid the issue, extract the bracketed number as text with regexextract(), like this:

=regexextract("A (2)", ".\d+.")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.