How to find the currency format in a formula

[AndrewBT]

I have an excel (Excel 2002) table with figures that are formatted using the currency format, and different formats have been chosen for the different currencies. Eg:

EUR 1,000
USD 1,500
GBP 2,000

The currency codes are a part of the cell format rather than entered text.

I want to have the currency text (eg EUR) in a separate cell so that I can work with it. The excel number to text conversion functions I've tried don't help because they require the number format to be known/specified in the function.

Those currency codes are staring me in the face, but I just can't get at them!

Any ideas?

Thanks

Andrew

 

[GlennUK]

Hi Andrew,

select the cell to the right of the first number, say cell B2, and do menu command Insert/Name/Define Name, and create a name of GetFormat. For the definition of the name type:

=GET.CELL(7,A2)

and add the name.
Now in cell B2, type =GetFormat

Adjust B2, and A2 in name definition to suit yourself.

 

[AndrewBT]

Thanks Glenn, that's done the job. I wasn't even close to that as a solution when I was looking.

Kind regards

Andrew

 

[Bobalingo]

Hi GlennUK
how would this work with either 2013 or 2016 please ?

thanks in advance
Bobalingo