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]

You could do it with a formula:

=IFERROR(SUBSTITUTE(A2,"Cr","")*1,0)+IFERROR(SUBSTITUTE(A2,"Dr","")*-1,0)

 

[RAJESH1960]

You could do it with a formula:

=IFERROR(SUBSTITUTE(A2,"Cr","")*1,0)+IFERROR(SUBSTITUTE(A2,"Dr","")*-1,0)

I am getting all zero.

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

Cell Formulas
Range	Formula
B2:B20	B2	=IFERROR(SUBSTITUTE(A2,"Cr","")*1,0)+IFERROR(SUBSTITUTE(A2,"Dr","")*-1,0)

 

[steve the fish]

Whats does =LEN(A2) produce?

 

[RAJESH1960]

Query to remove Dr Cr from amount.xlsx
	A	B	C
1	Round Off
2	0.50 Cr	0.00	3
3		0.00	0
4	0.02 Dr	0.00	4
5	0.02 Cr	0.00	4
6		0.00	0
7	0.22 Dr	0.00	4
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(SUBSTITUTE(A2,"Cr","")*1,0)+IFERROR(SUBSTITUTE(A2,"Dr","")*-1,0)
C2:C7	C2	=LEN(A2)

 

[steve the fish]

Oh ok. So this is just formatting. If you click into cell A2 what do you see in the formula bar?

 

[RAJESH1960]

Oh ok. So this is just formatting. If you click into cell A2 what do you see in the formula bar?

I see the number only not the text. How do I change the formatting.?

 

[steve the fish]

If you right click then format cells you should see its custom formatted. Just remove that. It shouldnt matter to calculations by the way. Just use the numbers as normal. People have been known to type numbers in following the format which would be wrong. Click a few cells to see whats in the formula bar. They should all be just numbers without the text.

 

[RAJESH1960]

Oh ok. So this is just formatting. If you click into cell A2 what do you see in the formula bar?

Sorry I thought it worked. But the all the numbers are still positive even the negative numbers are showing positive

 

[RAJESH1960]

How do I change the format to all the columns and rows at a time, Have you any idea.? There are more than 1900 rows and 26 amount columns to change....