How do I inhibit conversion of `(3)` to `-3` within a Google Sheets formula?

Question

=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?

Answer 1

What do you think about this? :

=index(split("A (2)","()"),2)

Answer 2

=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.

Answer 3

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+.")