check currency symbol (VBA)

[Felix Atagong]

I have a list of numbers, these are all currencies, although not all numbers are formatted as currency. The numbers are in the European format, thus a decimal point is in fact represented by a ,

If no currency formatting is put inside the cell, the currency is Euro.
But some cells have a currency symbol in front of them and I need to find out WHAT currency that is. The cells in question are not text, they have been formatted as currency, so I can't use the 'left' function to find out the first letter.

2345,67
$1.234,56
3456,78
£8765,43
€8513,94

So I need to know that cell 2 is dollars, cell 4 is pounds and cell 5 is euro... It should be extremely simple I guess, but I just can't find it...

 

[Andrew Poulsom]

I think you would need a UDF for that:

Function GetCurrency(Rng As Range) As String
    If IsNumeric(Left(Rng.Text, 1)) Then
        GetCurrency = ""
    Else
        GetCurrency = Left(Rng.Text, 1)
    End If
End Function

 

[Felix Atagong]

I knew it was (relatively) simple. Thanks, it works!

 

[BrianB]

You could try this custom function in another column. Copy/paste the code to a code module via Alt+F11 keys. You might like to try the alternative line of code.

Function MyFormat(rg As Range) As String
    f = Left(rg.NumberFormatLocal, 1)
    'f = left(rg.NumberFormat,1)     ' alternative
    Select Case f
        Case Is = "£"
            MyFormat = "sterling"
        Case Is = "$"
            MyFormat = "dollar"
        Case Is = "€"   ' euro character code 128
            MyFormat = "euro"
        Case Else
            MyFormat = "decimal"
    End Select
End Function

use a cell formula like :-
=myformat(A1)

 

[Felix Atagong]

As a matter of fact I put the code from Andrew in a vba module, because I do a lot of things with these amounts. The code looks a bit like your solution BrianB with select case to make the difference between Euro, Dollar and Pound.

Thanks All!

 

[pmich]

Andrew Poulsom, I wanted to check whether a dollar sign is present in a cell in which different people have entered with or without dollar sign. Your code helped me. thanks.