How To Extract Currency Symbol From A Custom Formatted Cell

Question

Hi,

I'm using Excel vers. 16.33 on OSX 10-14.6.

I download .xlsx files where in column C I have amounts like $123,00 or €234,65 or SEK98,77 or £55,32.

All cells are formatted as custom.

I need to know the currency related to each cell.

Have tried many solutions found in the forums but none of them works for me.

Appreciate your help.

Answer 1

Below is the function which you can use. You can download your file from Onedrive with code https://1drv.ms/x/s!AldvjX7HG_m7gS8ZbTFwNXdi-9l...

Function GetCurrency(Cell As Range) As String
    GetCurrency = Replace(Cell.NumberFormat, "\", "")
    GetCurrency = Mid(GetCurrency, 1, InStr(GetCurrency, "#") - 1)
End Function

Answer 2

Here is that formula working on my version of Excel:

.

Answer 3

Hi Xldummy

This formula should work for you, it will extract any characters before the first number in a cell, you will need to change the A1 cell reference as appropriate . . .

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

Answer 4

Hi Xldummy

I checked and that formula does work, unless you have a different regional setup on your PC, I notice your decimal separator is set to comma, rather than dot . . .

Do you use a semi-colon in formula rather than a comma?

if so, try this version of that formula:

=LEFT(A1;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))-1)

Answer 5

Thanks Dave but I get "The formula contains an error " message.