Any suggestions on how I could accommodate cells which contains "CR" appended, that it should reflect the equivalent numeric negative number instead, so that it is simpler to aggregate totals in the column ? Thanks in advance !
MacBook Air 13″, macOS 12.4
One way is to do something like this:
In D2, filled down:
=REGEX.EXTRACT(C2,"\d+.\d+")*IF(COUNTMATCHES(C2,"CR")>0,−1,1)
This extracts the number part and if it also finds "CR" multiplies the number by -1, otherwise by 1.
Once you've got the values you want you can select the cells with the formula, command-c to copy, followed by Edit > Paste Formula Results. After that you can delete the original column.
SG
Here a version with an additional column for the final amount.
Formula for cell D5: IF(RIGHT(C5,2)="CR",VALUE(TEXTBEFORE(C5, REGEX("[A-Z]+",FALSE),occurrence))×−1,C5)
Please check how your text with CR is really formatted, is it in 2 rows because the column is too small or is there something special.
Based on your region the , or the ; will be used to separate the different sections of a formula.
Hope this will solve your question, please let me know if something in unclear.
Ralf
Thanks for the solution via regular expressions which I am starting to learn. Much appreciation !
Thanks and much appreciation for the solution here too !
Converting cells which contains "CR" appended into the equivalent numeric negative value
