Remove Dr Cr from numbers

[RAJESH1960]

Hello guys
I have received a data sheet from one of my clients. The problem is I am not able to change the format of the figures and remove Dr & Cr. I require +ve and -ve numbers to finish my work. How is it possible to convert the custom format to number format. I tried selecting the whole column and selecting number format from the menu bar. But it is showing even the negative numbers as positive. Dr is a negative number and Cr is a positive number.

Query to remove Dr Cr from amount.xlsx
	A
1	Round Off
2	0.50 Cr
3
4	0.02 Dr
5	0.02 Cr
6
7	0.22 Dr
8	0.25 Dr
9	0.03 Dr
10
11	0.02 Cr
12
13	0.50 Cr
14	0.66 Cr
15	0.46 Dr
16	0.21 Dr
17	0.39 Dr
18	0.42 Dr
19	0.01 Dr
20	0.02 Dr
21	0.01 Cr
22	0.03 Cr
23
Sheet1

 

[RAJESH1960]

Something like this but must be run on a copy of the workbook to test!!

Sub Macro2()

Dim c As Range, lr As Long, lc As Long

With ActiveSheet
    lr = .Cells.Find(What:="*", After:=.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    lc = .Cells.Find(What:="*", After:=.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    For Each c In .Range(.Cells(1, 1), .Cells(lr, lc))
        If c.NumberFormat = """""0.00"" Dr""" Then
            If IsNumeric(c) Then
                c.Value = c.Value * -1
            End If
        End If
        c.NumberFormat = "General"
    Next
End With

End Sub

Wow!! That really worked. Thanks Steve. You just saved me a lot of time.

 

[steve the fish]

One thing i would say is keep a copy of the workbook before you ever run this. You are in a right mess if it should fail halfway through for whatever reason.

 

[RAJESH1960]

One thing i would say is keep a copy of the workbook before you ever run this. You are in a right mess if it should fail halfway through for whatever reason.

Yeah. I always do that. Thanks once again.