Solved

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

Posted on 2011-03-23
393 Views
Is there a way to determine if a cell has a US currency set?
0
Question by:rayluvs
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4

LVL 33

Expert Comment

ID: 35203352
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

LVL 33

Assisted Solution

jppinto earned 450 total points
ID: 35203429
Or you can use a VBA if the cell is formated as currency, like the attached example.

jppinto
Currency.xlsm
0

LVL 33

Assisted Solution

jppinto earned 450 total points
ID: 35203438
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

LVL 23

Assisted Solution

Michael Fowler earned 50 total points
ID: 35203445
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 Comment

ID: 35203662
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

LVL 33

Accepted Solution

jppinto earned 450 total points
ID: 35205556
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

Author Comment

ID: 35207421
Worked.

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

LVL 33

Assisted Solution

jppinto earned 450 total points
ID: 35208832
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 Comment

ID: 35209101
Thanx lots
0

Author Closing Comment

ID: 35209145
thanx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month1 day, 23 hours left to enroll