Recognize currency in a cell

[Marcin P]

Hi,

I have a spreadsheet which uses different currencies when data are
imported. I'm looking for a method to recognize currency type used in
a cell, for example:

A1: €100 or $100, £100 and so on.

Many thanks.
Marcin

 

[FSt1]

Hi,

I have a spreadsheet which uses different currencies when data are
imported. I'm looking for a method to recognize currency type used in
a cell, for example:

A1: €100 or $100, £100 and so on.

Many thanks.
Marcin

hi
currency type is formating so that is what you would be looking for.
here is a do nothing sniplet that checks for
dollars US.

Sub checkformat()
If Range("B2").NumberFormat = "$#,##0.00" Then
MsgBox "Dollars"
Else
MsgBox "NOT Dollars"
End If
End Sub

you would need to find out what your other currency formats are and
check for each one perhaps in a loop or
something.

Regards
FSt1

 

[minimaster]

hmm..
formating can vary a lot..
there is a better way Typename

Sub testttt4()
Dim c As Range
' Set c = ActiveSheet.[A1]
For Each c In ActiveSheet.UsedRange
If TypeName(c.Value) = "Currency" Then
c.Select
MsgBox " selected cell is a currency cell"
exit sub
End If
Next c
End Sub

 

[FSt1]

hmm..
formating can vary a lot..
there is a better way    Typename

Sub testttt4()
    Dim c As Range
'    Set c = ActiveSheet.[A1]
    For Each c In ActiveSheet.UsedRange
        If TypeName(c.Value) = "Currency" Then
            c.Select
            MsgBox " selected cell is a currency cell"
            exit sub
        End If
    Next c
End Sub

hi,
i took it that he was looking for different currency types not just
currency.

Perhaps the op will clarify for us.

Regards
FSt1

 

[Gord Dibben]

I'm not sure about this.

How should Excel know the currency format for each cell of data imported?

If raw numbers, no format would be displayed.

If the data OP is grabbing comes in with different currency types displayed, I
would say the data is in Text form.

Maybe OP should be looking at LEFT and CODE functions??

=CODE(LEFT,A1) would return 163 for English Pound £

128 for Euro sign €

Of course OP would have to have a table with codes for all currency types
involved.

Easy enough to build one if that's the case.


Gord Dibben MS Excel MVP

hmm..
formating can vary a lot..
there is a better way    Typename

Sub testttt4()
    Dim c As Range
'    Set c = ActiveSheet.[A1]
    For Each c In ActiveSheet.UsedRange
        If TypeName(c.Value) = "Currency" Then
            c.Select
            MsgBox " selected cell is a currency cell"
            exit sub
        End If
    Next c
End Sub

hi,
i took it that he was looking for different currency types not just
currency.

Perhaps the op will clarify for us.

Regards
FSt1