IF Formula

[Excel_Assis]

Can you help me with a formula that work on the following:

If the data entered in a cell shows a B like B1543.87 it would be converted to US$ but if the data entered in the cell is $1543.87 then no action taken.

 

[Krishnakumar]

Hi,

In B1

=IF(LEFT(A1)="b",--MID(A1,2,25),A1)

and format the cell

HTH

 

[Excel_Assis]

Krishnakumar thank you for your reply. Could I ask you to please explain how this formula works.

 

[Krishnakumar]

Hi,

LEFT(A1) will give you the first character of A1

if it's "b" then the next step

MID(A1,2,25) will give everything after the first character (25 can be replace with len(a1))

now the result will look like "1543.87". This is not a TRUE number. To convert this text value into a number we put '--' in front of MID.

It can also be written as

MID(A1,2,25)+0

MID(A1,2,25)*1

HTH

 

[Excel_Assis]

Krishnakumar thanks again for the reply, however not exactly what I want to do. If the number has a B at the beginning like B1234.00 then convert to us$ so a calculation has to take place but if the is a $ at the beginning of the number then do nothing as the amount is already in US$.

This is so I can get amounts entered both foreign and US$ in to US$ then add them to get a total.

 

[Krishnakumar]

Hi,

See the screen shot. If this is not what you are after, please explain

Excel Workbook
	A	B
1	B1234.0	$1,234.00
2	$1,234.00	$1,234.00
Sheet1

 

[Sanjeev1976]

I think you may be looking for a different solution.

Then in continuity to Krishnakumar's reply, just change the formula as below :

=IF(LEFT(A1)="B",--MID(A1,2,25)/????,A1)

Put the currency exchange conversion figure instead of ???? and you will get your desired result.

 

[Excel_Assis]

Sanjeev1976 thank you that is correct. Krishnakumar thank you too for your help Sanjeev1976 understood what I was looking to do. You have to add an exchange rate to convert to US$ not just change the symbol.