CELL FORMAT - IF CONDITION

Suresh Kusunuri

Board Regular
Joined
Dec 28, 2007
Messages
124
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
Save the Excel workbook as CSV and open the file.
You will see Cr and Dr attached with Figures Then use if function there
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
Hi @MARK858 The formatting is generated by an accounting software. Dr means Debit and Cr means credit entries
 

Suresh Kusunuri

Board Regular
Joined
Dec 28, 2007
Messages
124
Office Version
  1. 2007
Platform
  1. Windows
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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
ABC
1123.00 Cr145#VALUE!
xxxx
Cell Formulas
RangeFormula
C1C1=IF((RIGHT(A1,2)="Cr"),(B1+A1),"")
 
Last edited:

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You don't need a helper column with the CSV method, just an amendment to the formula like...

xxxx.csv
ABC
1123.00 Cr145268
xxxx
Cell Formulas
RangeFormula
C1C1=IF((RIGHT(A1,2)="Cr"),B1+LEFT(A1,LEN(A1)-2),"")
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2019
Messages
870
Office Version
  1. 365
Platform
  1. Windows
You don't need a helper column with the CSV method, just an amendment to the formula like...

xxxx.csv
ABC
1123.00 Cr145268
xxxx
Cell Formulas
RangeFormula
C1C1=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,602
Messages
5,523,831
Members
409,539
Latest member
Re1ease

This Week's Hot Topics

Top