# How to determine if a format has US dollar sign in Excel

Is there a way to determine if a cell has a US currency set?
###### Who is Participating?

x

Commented:
I've created a UDF (User Defined Function) that will work like this:

=HasDollar(F10)

It will return True or False depending if the cell has a \$ sign in it. The code is like this:

Function HasDollar(cell As Range) As Boolean

If InStr(1, cell.Text, "\$") > 0 Then
HasDollar = True
Else
HasDollar = False
End If

End Function

You have to insert it into a VBA Module so that you can call it on your sheet. Please check the attached working example.

jppinto

DetectCurrency.xlsm
0

Commented:
If the value is on cell A1, you can check if the dollar sign is on the first position with this:

=IF(LEFT(A1,1)="\$","True","False")

jppinto
0

Commented:
Or you can use a VBA if the cell is formated as currency, like the attached example.

jppinto
Currency.xlsm
0

Commented:
Or even like this (another VBA version).

Sub test()

If InStr(1, Range("a1").Text, "\$") > 0 Then
' US currency
MsgBox ("US Currency")
End If

End Sub

Currency.xlsm
0

Solutions ConsultantCommented:
The formula above works.

You can also
- check manually by right clicking on the cell selecting format cells and looking at the current formatting
- vba you can use something like the attached code

Michael
``````Function hasDollarFormat(Cell As Range) As Boolean
If Left(Cell.NumberFormat, 1) = "\$" Then
hasDollarFormat = True
Else
hasDollarFormat = False
End If
End Function
``````
0

Author Commented:
Thanx, and it perfect if I needed a button, but what I need is within the Excel in report format.  I created an Excel with jppinto initial formula...

Hope this helps
DetectCurrency.xlsx
0

Author Commented:
Worked.

Just to make sure, there is no Excel function that does this right (we have to create the function)?
0

Commented:
Right! No Excel function for this! You need VBA...

You see a value on a cell formated like currency like "\$ 450" but the real value of the cell is "450". So you can't get the left most character from the cell because it would return 4 instead of the \$ sign, unless the value on the cell would be a text value and it's real value is like "\$ 450". Then you could use a LEFT(A1,1)="\$" to check if there is a \$ sign, wich is not the case.

jppinto
0

Author Commented:
Thanx lots
0

Author Commented:
thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.