Formula based on currency symbol

[Fi_83]

Hi there,

I have a sheet of data on column J i have a list of prices some in Euro some in GBP with either a £ or €
I have created a new column K and want to covert these all to the same currency of dollar under the rate of use 1.2 for GBP and 1.1 for Euros

Can anyone help me.? I have never used conditional formatting to insert a formula but think this might be the solution?

 

[shaowu459]

Book1.xlsx
	A	B	C
1	£800	960
2	€ 900	990
3	£100	120
4	€ 200	220
5	Rate of use 1.2 for GBP and 1.1 for Euros
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=LOOKUP(9^9,--RIGHT(A1,ROW($1:99)))*IF(LEFT(A1)="£",1.2,1.1)

 

[Fi_83]

Thank you so much for reply
sorry i am being really dumb here. ..What is the formula if my £ and € amounts are in J13 to J500 and my conversion to $ to be in K13 to K500

 

[shaowu459]

Assume J13 is a text "£800".

Book1.xlsx
	J	K
13	£800	$960.00
14	€ 900	$990.00
15	£100	$120.00
16	€200	$220.00
Sheet2

Cell Formulas
Range	Formula
K13:K16	K13	=LOOKUP(9^9,--RIGHT(J13,ROW($1:99)))*IF(LEFT(J13)="£",1.2,1.1)

 

[Fi_83]

Thank you!!! I dont understand it, but the formula works great

 

[shaowu459]

Thank you!!! I dont understand it, but the formula works great

Glad to help