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

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

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

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

Pete

On Jan 30, 1:10*pm, vud i <vud wrote:
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 ... ???

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

With your values in column A, In B1 enter:

=--LEFT(A1,LEN(A1)-3) and copy down
--
Gary''s Student - gsnu200829


"vud i" wrote:

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

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

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.)


New Delhi, India


"vud i" wrote:

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

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

Try this:

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

Regards,
Fred.

"vud i" <vud wrote in message
...
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 ... ???

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

How nostalgic to see COBOL formats!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vud i" <vud wrote in message
...
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 ... ???

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

€œ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

Garys 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

"vud i" wrote:

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