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

 

[steve the fish]

Just highlight all the cells with button top left of sheet then right click, format cells, change it to general or number.

 

[RAJESH1960]

Just highlight all the cells with button top left of sheet then right click, format cells, change it to general or number.

That is what I did in the first instance. As I said all the amounts display positive numbers only. Hence, the round off and all other columns won't match. At least if I could sort the data with dr and cr amounts it would be easier to work. But that too doesn't work.

 

[steve the fish]

Oh. You are in a little trouble then. You have different formats randomly around the worksheet but the actual underlying numbers are all positive. If you click a positive number the format is the Cr one but im assuming the Dr ones have the other format. You are going to need to use code to fix that. It isnt possible otherwise unless you go through them one by one.

 

[Dave Patton]

N.B. You specified Dr is a negative number and Cr is a positive number

2 approaches to consider
You can use a temporary column; Insert a column
Enter the formula depending on whether of not the custom formatting shows Dr or Cr.
Copy the formula down to the relevant cells for Dr
Copy the formula down to the relevant cells for Cr
Format the cells as numbers
Convert the column to values
Check that the numbers are correct and then delete column A

or you copy a cell that contain -1 and Paste Special multiply to the relevant cells

T202106a.xlsm
	A	B
1	Round Off
2	0.50Cr	0.50
3	0.02 Dr	-0.02
6c

Cell Formulas
Range	Formula
B2	B2	=A2
B3	B3	=-A3

 

[steve the fish]

Im not sure thats much help Dave. May as well just type them. They are randomly assigned different formats all over the worksheet from what i can tell.

 

[Dave Patton]

Steve I didn't see the post 10.

I don't have a suggestion based on the information provided.

If the data is a financial report, it should balance and the Dr or Cr formatting may be irrelevant.

 

[steve the fish]

From what i can tell all of the numbers are positive but have different formatting randomly, one changing them to Cr the other Dr. Would have to be vba i think.

 

[RAJESH1960]

From what i can tell all of the numbers are positive but have different formatting randomly, one changing them to Cr the other Dr. Would have to be vba i think.

Actually , This is a sheet exported in excel format from another software where the amounts are shown in Dr and Cr instead of -ve and +ve figures. I am just trying to figure out how to replace the Dr amounts with negative numbers and Cr amounts with positive numbers.

 

[steve the fish]

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

 

[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

Yes Steve. That worked in the query sheet. Let me try and check in the original data.