Change CR after digits to - before digit?

Change CR after digits to - before digit?

Didn't find your answer?

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.

avatar
By The Limey
19th Feb 2013 10:35

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])'

Thanks (0)
avatar
By User deleted
19th Feb 2013 12:14

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"

Thanks (1)
avatar
By tugwilson
20th Feb 2013 11:02

Thanks to all.

Thought about this OGA, but need to keep the structure without resorting. Thanks The Limey.

 

Thanks (0)
avatar
By mikeopolo
01st Mar 2013 04:13

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.

Thanks (0)
avatar
By ACDWebb
01st Mar 2013 08:34

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

Thanks (0)