Removing Currency Sign in the Equation

[Francis Gerard Tupaz]

Dear Excel Guru's,

May I seek your assistance please in the attached excel file that am working on?

This is suppose to compute for potential earnings "region or country specific" currency rate.

Example: If you put "Japan" in under REGION (C6) the Local Currency Rate will change to "JPY" (G6), and automatically the columns D1014 will likewise adopt currency rate. And it is suppose to change as you change the regions i.e Europe 1, Europe 2, etc. Source data is located in product code sheet. I am getting an error if I multiply D10*E10 right now.

Is there a way I can delete the currency sign under B21 so it will just consider the number? Or is there a way that it will multiply even with the currency?

Thanks very much, hoping to hear from you my friends.

Regards,

Francis

 

[tommyt61]

Here is one option . I just added a hidden column (E) and entered formula to trim off the currency symbol. then just multiplied by e10 instead of d10 in Cell b21.

=RIGHT(D10,LEN(D10)-1)

i attached a file if you want to see it work firsthand.

 

[tommyt61]

It is treating your currency numbers in your Product Code sheet as text. did you happen to copy and paste these into those cells.

you can see what i am talking about by doing the following.

Example: use the japanese currency cells that your demo file is referring to.

1. delete all data on the Product Code sheet in cells D71- D75
2. set cell format to general
3. re-enter your values 650,1150,1500,3400,650
4. highlite D71-D75
5. set cell format to currency and select japanese
6. make sure you have it set to 2 places ..it will mess up your trim(index) formula on Purchase Request sheet

Now go to your Purchase Request sheet and set the cell formats like you want them in d10 and b21

it should now multiply correctly , at least it did for me.

hope it helps

 

[Francis Gerard Tupaz]

Dear Tommy and Excel Guru,

Thank you for the help, sadly I still am getting an error after doing all the suggestions whenever I change the regions it no longer computes.

Hope someone can help me.

Regards,

Francis

It is treating your currency numbers in your Product Code sheet as text. did you happen to copy and paste these into those cells.

you can see what i am talking about by doing the following.

Example: use the japanese currency cells that your demo file is referring to.

1. delete all data on the Product Code sheet in cells D71- D75
2. set cell format to general
3. re-enter your values 650,1150,1500,3400,650
4. highlite D71-D75
5. set cell format to currency and select japanese
6. make sure you have it set to 2 places ..it will mess up your trim(index) formula on Purchase Request sheet

Now go to your Purchase Request sheet and set the cell formats like you want them in d10 and b21

it should now multiply correctly , at least it did for me.

hope it helps

 

[tommyt61]

Can you be more specific , you mention after making changes it no longer computes. I attached a copy of your demo file that i made the exact changes i listed . See if it works correctly for you .

 

[tommyt61]

Or in your file you can put this formula in B21 and copy down to B25. This will just trim the currency symbol off of the number in D10-D14 before you multiply by 10. set your cell format on B21-B25 to number no spaces .

=RIGHT(D10,LEN(D10)-1)*10

 

[tommyt61]

Hey Francis,

I went back and looked at your sheet and realized that you have a bunch of different currency formats. so the options i gave you was me assuming that all amounts would only have 1 symbol in front of the number so the options i provided will not work . sorry i didn't pick up on that. i would not have wasted your time messing with my suggestions.

Here is where your problem lies . a Vlookup function will not bring the data format into cell D10 as a number. Vlookup displays it's data as text only. You can't multiply text *10 without getting an error, you have to multiply number * number only. to test this pick a blank cell in Purchase Request sheet and enter this formula =ISTEXT(D10) If you get a True than it sees the data in D10 as text. If you get a False it sees the data as a number.

you have to get the data in Cells D10-D14 in number format. currently using Vlookup is not gonna work. If you can deal with not having the currency symbol in cells D10-D25 it might not be to hard to find a workaround. hope this helps answer some questions for you .

 

[tommyt61]

Take a look at this . i went through each currency and everything seemed to calculate correctly... but i have said that before haven't i

 

[Francis Gerard Tupaz]

SOLVED: Removing Currency Sign in the Equation

Hi Tommy,

Again I owe you a big big thanks! I was able to use the formula you sent tweaked it a bit so it will just be under one cell all together but your guidance has helped me SO MUCH!

May you continue to prosper!

Regards,

Francis

Take a look at this . i went through each currency and everything seemed to calculate correctly... but i have said that before haven't i