Hello Excel experts.
The software client uses transfers credit balances to excel as, for example, 152.50CR. Is there a quick way, in one swoop, to change it to -152.50 so I can use it in formulas etc.
Thanks you muchly in urgent anticipation.
Replies (5)
Please login or register to join the discussion.
You'll need a new column with an equation in it. Try: '=if(right([cell reference],2)="CR",1*[left([cell reference],len([cell reference])-2),[cell reference])'
You could just ...
Filter the column to get all the CR's together
Then find and replace CR with 0
then a new column with formula "=0-cell reference"
Macro to convert 999CR to -999
The following macro will turn all "999cr" values in a selected range into negatives
Sub cr2minus()
'Turn 999.99cr to -999.99
Dim target, cell As Range
Set target = Range(Selection.Address) 'User must select range first
For Each cell In target
If InStr(UCase(cell.Value), "CR") <> 0 Then
cell.Value = -Left(cell.Value, Len(cell.Value) - 2)
End If
Next cell
End Sub
One way to make use of this macro is:
- copy this text
- In Excel, press Alt+F11 to open Visual Basic
- Select Insert, Module from the menu bar
- Paste the text and close the VB window
- Back in the report, SELECT YOUR DATA & press Alt+F8 to open a list of macros
- Select "cr2minus" and click Run.
All numbers with "cr" attached will be converted to negatives. The advantage of a macro is that no extra cells are required.
The addin
called Change Case HERE will do it for you by using the Math Operation "Make Negative" in conjunction with "Remove Characters" set to 2 from the right.
I suspect that you would find something similar in ASAP Utilities
Alternatively as in OGA suggestion Find and Replace CR with nothing in selected cells and then Copy -1 from another cell and, PasteSpecial Multiply the cells you want to make negative