Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... ??? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... ??? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... ??? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... ??? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... ??? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... ??? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... ??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
=IF(D13="PAID","YES","NO") Can I change fonts colour | Excel Discussion (Misc queries) |