In custom functions Convert "Dr" number to -ve number & "Cr" number to +ve number
HI
from accounts report I get values ends with "Dr" & "Cr" Can I convert "Dr" values to -ve & "Cr" to +ve in a same column so that -ve values display as -ve numbers and +ve values as +ve numbers ( without end "Dr" or "Cr" )
SMS
Roopesh K Goswami
New Delhi
Yes you can, but it would help if you could provide a sample workbook.
If I've been of help, please hit the star
HI
from accounts report I get values ends with "Dr" & "Cr" Can I convert "Dr" values to -ve & "Cr" to +ve in a same column so that -ve values display as -ve numbers and +ve values as +ve numbers ( without end "Dr" or "Cr" )
15,456.00 Dr
85,556.00 Dr
14,321.00 Dr
9,833.00 Cr
1,387.00 Dr
1,303.00 Cr
41,613.00 Dr
4,163.00 Dr
1,613.00 Cr
456.00 Dr
SMS
Roopesh K Goswami
Hi,
Here's ONE way of doing it.
Assuming your example data above starts in cell A1, enter the below formula in B1 and copy down.
=IF(RIGHT(A1,2)="Dr",LEFT(A1,LEN(A1)-3),IF(RIGHT(A1,2)="Cr",-(LEFT(A1,LEN(A1)-3))))
Ensure column B is formatted as "currency".
You can then paste values over the original numbers if you so desire.
If you need them to change in place then you will have to put out a call for a VBA expert.
Hope it's of some help.
Re: In custom functions Convert "Dr" number to -ve number & "Cr" number to +ve number
Actually the term "Dr" & "Cr" is like in custom function in formatting cell as 0.00 "Dr" and 0.00 "Cr"
Roopesh K Goswami
And what decides which cells are formatted as Cr and Dr?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks