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

[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 ... ???

 

[Pete_UK]

Well, you could post some examples of your data, so that we can see
what it looks like.

Pete

 

[Gary''s Student]

With your values in column A, In B1 enter:

=--LEFT(A1,LEN(A1)-3) and copy down

 

[DILipandey]

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

 

[Fred Smith]

Try this:

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

Regards,
Fred.

 

[Bernard Liengme]

How nostalgic to see COBOL formats!

 

[vud i]

“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