Formula Assistance Financial system export
 

I have a listing of over 2500 numbers that I exported from our financial
system. Some of the cells have a CR after the number standing for credit
balance instead of a negative. Some of the numbers are positive and have
nothing else in the cell besides the number. How can I change the numbers
with a CR to show paranthesis around the number instead of the CR? I would
be happy to forward you a sample.

We are using Office 2007.

Toysam

This is a common problem with data from finance systems. Try the code below by pasting it in your macro visual basic editor

You will need to change 3 things

1 In the first line the sheetID from "sheet1" if yours differs
2 Also in line 1: the range"b2:b80000" if your data is in a different column to "b"
3 The row 3 lines from bottom "B:B" to whatever column your data is in

Then run as a macro


Sub ChangeCR()


For Each cell In Worksheets("Sheet1").Range("b2:b80000").Cells
If UCase(Right(cell.Value, 2)) = "CR" Then
cell.Value = -Val(Left(cell.Value, Len(cell.Value) - 2))
Else
End If

Next cell
Columns("B:B").Select
Selection.NumberFormat = "0.00;(0.00)"
Range("B2").Select

End Sub