CELL FORMAT - IF CONDITION

[Suresh Kusunuri]

My Cell Format is ""0.00" Cr". But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other programme. Now, I want to get the value from this cell to another cell based on “Cr” by using IF condition. But “Cr” is not appeared in the cell for applying formula. Can anybody help me?

Thanks
Suresh

 

[CA_Punit]

Save the Excel workbook as CSV and open the file.
You will see Cr and Dr attached with Figures Then use if function there

 

[CA_Punit]

Hi @MARK858 The formatting is generated by an accounting software. Dr means Debit and Cr means credit entries

 

[Suresh Kusunuri]

There are 2 types of values in the same column. 1 is positive and another is negative. "Dr" stands for positive and "Cr" stands for Negative. All are numeric.

 

[CA_Punit]

Try Post 11

 

[MARK858]

Try Post 11

Brings it back as text for me and so doesn't allow the sum, would have to amend the formula to remove the Cr for it to work (i.e. =IF((RIGHT(A1,2)="Cr"),B1+LEFT(A1,LEN(A1)-2),"").....)

xxxx.csv
	A	B	C
1	123.00 Cr	145	#VALUE!
xxxx

Cell Formulas
Range	Formula
C1	C1	=IF((RIGHT(A1,2)="Cr"),(B1+A1),"")

 

[Suresh Kusunuri]

Save the Excel workbook as CSV and open the file.
You will see Cr and Dr attached with Figures Then use if function there

Thank you Mr. Punit, But my purpose is not served.

 

[CA_Punit]

I meant "Dr" or "Cr" which were only visible in excel as it was a custom formatting. converting the data into CSV will hard code the "Dr" or "Cr" in the cell so you can use the column and use the data as an helper column to derive what is debit and what is credit

 

[MARK858]

You don't need a helper column with the CSV method, just an amendment to the formula like...

xxxx.csv
	A	B	C
1	123.00 Cr	145	268
xxxx

Cell Formulas
Range	Formula
C1	C1	=IF((RIGHT(A1,2)="Cr"),B1+LEFT(A1,LEN(A1)-2),"")

 

[CA_Punit]

A

Thank you Mr. Punit, But my purpose is not served.

After saving a copy of your file as CSV open the file and paste the column which you require to the next of the column in your main excel file. Now you have a reference cell which has debit and cr hard coded in cell. Now your formula will work keeping cell reference for Dr and Cr in the helper column

 

[CA_Punit]

You don't need a helper column with the CSV method, just an amendment to the formula like...

xxxx.csv
	A	B	C
1	123.00 Cr	145	268
xxxx

Cell Formulas
Range	Formula
C1	C1	=IF((RIGHT(A1,2)="Cr"),B1+LEFT(A1,LEN(A1)-2),"")

But the probelm with CSV file is when it is saved it removes all the formula and Additional sheet.