[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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?
0
rayluvs
Asked:
rayluvs
  • 5
  • 4
5 Solutions
 
jppintoCommented:
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
 
jppintoCommented:
Or you can use a VBA if the cell is formated as currency, like the attached example.

jppinto
Currency.xlsm
0
 
jppintoCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Michael FowlerSolutions 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

Open in new window

0
 
rayluvsAuthor 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
 
jppintoCommented:
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
 
rayluvsAuthor Commented:
Worked.

Just to make sure, there is no Excel function that does this right (we have to create the function)?
0
 
jppintoCommented:
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
 
rayluvsAuthor Commented:
Thanx lots
0
 
rayluvsAuthor Commented:
thanx
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now