Format Currency Cell

[Yossy]

I have a column that is cell formatted in currency. I however want to pull
information from the column to another column picking their respective
currecy code but am unable to do that since the format is not showing
Currency. Any help and idea would be appreciated.

I want to use vlookup to pull up information from the coulmn on just the
currency but since currency is formatted I cannot. HELPPPP

Thanks a big bunch.

 

[Sheeloo]

Your cell show $ 2.00 and you want to extract $?

Pl. give an example of what the cell has, what the format is and what you
want to get...

 

[Spiky]

Why does the format matter? Can't you change the format as needed?

If you have multiple currencies and need to calculate between them,
you probably should have the name of the currency at the top of the
column (or beginning of row) for visual reference, or Vlookup
reference. That would be easier than trying to make sure the numbers
are formatted with the proper symbol.

Or maybe I am completely misunderstanding you.

 

[Yossy]

My cell shows USD 2.00
CAN 2.00
EUR 50.00

just want to pick nly the CAN, EUR, USD, e.t.c using vlookup but cant since
it is formatted. I have thousands of rows. Any help totally appreciated. I am
using Excel 2007.

Thanks

 

[Spiky]

If it is always formatted like that, you can use the LEFT function to
look at the first 3 characters in the cell. But what exactly do you
need to do with it?

You said you want to pick the currency. Is that just to show it in
another cell? Are you try to add up all the EUR? Is the currency the
lookup_value in your Vlookup function to then look up something
elsewhere?

 

[Yossy]

you are misunderstanding me. Excel 2007 allows formatting of currency e.g CAN
200, EUR 200, USD 200. Although I can see these currency format they are not
in the cell so even if I use the left formula it doesn't pick itup. This goes
back to my question.

My cell shows USD 2.00
CAN 2.00
EUR 50.00

just want to pick only the CAN, EUR, USD, e.t.c using vlookup but cant since
it is formatted. Any help would be totally appreciated.

Thanks

 

[Spiky]

Oh, I see. Sorry for misunderstanding.

None of the built-in formulas can recognize the format like that. CELL
is the only one that can at all, and it cannot distinguish between
currencies like you want. You would need a UDF.

If you search at download.com for "morefunc", that set of UDFs
includes a function called XLM.GET.CELL, which can return the format
with more detail. So a formula to return "USD" from a cell showing
"USD 2.00" would be:
=LEFT(XLM.GET.CELL(53,A1),3)