Delete "CR" at the end of a number and make the # negative?

[smduello]

The program I am using is placing a "CR" at the end of every negative
number to denote that it is a "credit". This poses a problem when
using the data, as Excel won't recognize the number. I need to get/
write a macro that deletes the "CR" and makes the number negative. I
tried the "Left" commands in excel, but it won't work since you have
to specify how many numbers numbers are to the left and that changes
depending on the size of the number. Any ideas?

 

[Tim879]

try this
=if(exact(Right(a1,2),"CR"),value(left(a1,len(a1)-2)*-1),a1)

 

[Guest]

You may not really need a macro for this...

Assuming the "numbers" end with " CR"(a space followed by CR)

Try this:
Select the range to be impacted

Then...From the Excel main menu:
<edit><replace>
Find what: CR<-----a space followed by CR
Replace with: -
Click [Replace All]

one more step....
<data><text-to-columns>
Click [Next] until Step_3_of_3
Check: General
(If you click the [Advanced] button, you'll see that Excel's default is to
convert numbers with trailing minus signs to negative numbers)
Click [Finish]

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Rick Rothstein \(MVP - VB\)]

Tim has given you a method to do it with a spreadsheet formula coupled with
a help column. However, if you need to physically change the values in their
current cells, then this macros should do that for you (just set the
appropriate range in the For Each statement line)..

Sub RemoveCR()
Dim R As Range
For Each R In Range("A1:A200")
If R.Value Like "*[Cc][Rr]" Then
R.Value = -Left(R.Value, Len(R.Value) - 2)
End If
Next
End Sub

Rick

 

[smduello]

Tim has given you a method to do it with a spreadsheet formula coupled with
a help column. However, if you need to physically change the values in their
current cells, then this macros should do that for you (just set the
appropriate range in the For Each statement line)..

Sub RemoveCR()
Dim R As Range
For Each R In Range("A1:A200")
If R.Value Like "*[Cc][Rr]" Then
R.Value = -Left(R.Value, Len(R.Value) - 2)
End If
Next
End Sub

Rick

The program I am using is placing a "CR" at the end of every negative
number to denote that it is a "credit". This poses a problem when
using the data, as Excel won't recognize the number. I need to get/
write a macro that deletes the "CR" and makes the number negative. I
tried the "Left" commands in excel, but it won't work since you have
to specify how many numbers numbers are to the left and that changes
depending on the size of the number. Any ideas?- Hide quoted text -

- Show quoted text -

I used this, and tried to add the ActieSheet command to apply to
multiple worksheets, but its not working for me. I've looked at the
code several times. Ideas?

 

[Gord Dibben]

The code will run on any sheet that is active.

Do you want to run it on multiple selected sheets or all sheets in the workbook?

Sub RemoveCR()
Dim R As Range
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
'If only some sheets are selected change the above line to
'For each ws in ActiveWindow.SelectedSheets
For Each R In ws.Range("A1:A200")
If R.Value Like "*[Cc][Rr]" Then
R.Value = -Left(R.Value, Len(R.Value) - 2)
End If
Next R
Next ws
End Sub


Gord Dibben MS Excel MVP

Tim has given you a method to do it with a spreadsheet formula coupled with
a help column. However, if you need to physically change the values in their
current cells, then this macros should do that for you (just set the
appropriate range in the For Each statement line)..

Sub RemoveCR()
Dim R As Range
For Each R In Range("A1:A200")
If R.Value Like "*[Cc][Rr]" Then
R.Value = -Left(R.Value, Len(R.Value) - 2)
End If
Next
End Sub

Rick

The program I am using is placing a "CR" at the end of every negative
number to denote that it is a "credit". This poses a problem when
using the data, as Excel won't recognize the number. I need to get/
write a macro that deletes the "CR" and makes the number negative. I
tried the "Left" commands in excel, but it won't work since you have
to specify how many numbers numbers are to the left and that changes
depending on the size of the number. Any ideas?- Hide quoted text -

- Show quoted text -

I used this, and tried to add the ActieSheet command to apply to
multiple worksheets, but its not working for me. I've looked at the
code several times. Ideas?