I have google sheets, and trying to create a function that will only get rid of the () when they are there and times the number by a negative only when i get rid of the (), but if i don't have any or don't get rid of any it can just go on and complete the function i have below.

=IF(RIGHT('Cash Flow'!B17,1)="B",LEFT('Cash Flow'!B17,LEN('Cash Flow'!B17)-1)\*1000000000,IF(RIGHT('Cash Flow'!B17,1)="M",LEFT('Cash Flow'!B17,LEN('Cash Flow'!B17)-1)\*1000000,'Cash Flow'!B17))

CodePudding user response：

you can remove parenthesis this way:

=REGEXREPLACE(B1; "[\(\)]"; )

CodePudding user response：

Would something like this be helpful? I put your formula in a LAMBDA, so it checks if there's () with REGEXMATCH and returns the string without parentheses and the minus sign, or else returns the original string. And to that result applies the calculations of millions and billions

=LAMBDA(v,IF(RIGHT(v,1)="B",LEFT(v,(LEN(v)-1)\*1000000000,IF(RIGHT(v,1)="M",LEFT(v,LEN(v)-1)*1000000,v)))(IF(REGEXMATCH('Cash Flow'!B17,"\(|/)"),"-"&REGEXREPLACE('Cash Flow'!B17,"[()]",""), 'Cash Flow'!B17))