Hi I was wondering how I go about removing $ from a bunch of cells in an excel spreadsheet. I have written the following code based on my experience with Visual Basic 2005 but it give a Compile Error - Invalid Qualifier with cellValue highlighted in 'If cellValue.StartsWith("$") Then' line:

Sub remove_dollar_sign()
' Macro to remove $ from beginning of cell values
' Run Macro by pressing Ctrl+Shift+D

' Use string.StartsWith(substring) to find out if
' string starts with $
' Then use string.TrimStart([trimChars]) to remove
' $ from beginning of string

' Begin at row 4, column 3
' End at row 1098, column 5

Dim dollarChar As String
Dim cellValue As String

x = 4

Do Until x > 1098

For y = 3 To 5
cellValue = Cells(x, y).Value

If cellValue.StartsWith("$") Then
cellValue = cellValue.TrimStart("$"c)
Cells(x, y).Value = cellValue
End If
Next y

x = x + 2


End Sub

Re: Remove $ sign from cells in Excel Spreadsheet using macro/VBA


I don't recall VBA has any member method for String class. You can use

Code Snippet

If Left(cellValue, 1) = "$" Then

Cells(x, y).Value = Right(cellValue, Len(cellValue) -1)

End If