You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
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
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
