How to change "xxx CR" and "y,yyy DB" to "+xxx" and "-yyyy"

  • Thread starter Thread starter vud i
  • Start date Start date
V

vud i

I have copied a printed report to Excel. For further calculations, I need to
change the credit and debit notations to positive and negative numbers or
numerical text. To do this, one must ... ???
 
Well, you could post some examples of your data, so that we can see
what it looks like.

Pete
 
Hi Vud,

Try following:-

Enter in Cell A1 = xxx CR
you can also change it to yyyy CR or abcd DR or anything...

Enter in Cell B1 =
IF(ISNUMBER(FIND("cr",A1)),RIGHT(SUBSTITUTE(A1,"cr","+"),2)&"
"&MID(SUBSTITUTE(A1,"cr","+"),1,FIND("
",SUBSTITUTE(A1,"cr","+"))-1),RIGHT(SUBSTITUTE(A1,"dr","-"),2)&"
"&MID(SUBSTITUTE(A1,"dr","-"),1,FIND(" ",SUBSTITUTE(A1,"dr","-"))-1))

Let me know if it works.. thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India
 
Try this:

=IF(RIGHT(A1,2)="CR",-(LEFT(A1,LEN(A1)-3)),--(LEFT(A1,LEN(A1)-3)))

Regards,
Fred.
 
“vud i†should have been “bud i†– Visual problems.

Those were excellent responses.

.. Two examples of the strings involved are
1,551,960.00 CR & 334,770.00 DB

Gary’s Student got me started on the correct track, and Fred Smith finished
it for me. Following is my final solution:
=IF(RIGHT(G40,2)="CR",1*LEFT(G40,LEN(G40)-3),-LEFT(G40,LEN(G40)-3))

The "... ,1*LEFT ..." converts the numeric string into a positive number,
just as the "-" does in the next expresion.

… and Bernard, you must have been around for a few years. I have extensive
Commercial Translator and Cobol experience, but my real forté is FORTRAN.

bud i
 
Back
Top