VBA : Convert to number

Question

I tried to let a table cell's value assigned to String

code like this:

databodyrange.cells(2,2).value="$50.00"

After that,I got the same problem in VBA: Convert Text to Number

I tried the method in this question,but it did not work

The Excel I used is the 365 version.

How can I convert the text to number after assignment.

Answer 1

Using INT() or cINT(), you can convert a string into a number.

databodyrange.cells(2,2).value="$50.00"
debug.print INT(databodyrange.cells(2,2).value)
debug.print cINT(databodyrange.cells(2,2).value)

Either result: 50

Answer 2

Convert Currency String to Number

• You may have to adjust the separators. Use the test procedure at the bottom to do it.

Option Explicit

Function CurrencyToNumber( _
    ByVal CurrencyString As String, _
    Optional ByVal DecimalSeparator As String = ".", _
    Optional ByVal ThousandsSeparator As String = ",") _
As Double
    
    Dim csLen As Long: csLen = Len(CurrencyString)
    
    Select Case csLen
    
    Case 0
        Exit Function ' vbNullString
    
    Case 1
        If IsNumeric(CurrencyString) Then
            CurrencyToNumber = CDbl(CurrencyString)
        End If
    
    Case Else
        
        Dim rString As String
        Dim csStart As Long
        
        If Left(CurrencyString, 1) = "-" Then
            rString = "-"
            csStart = 2
        Else
            csStart = 1
        End If
        
        Dim cPos As Long
        Dim cChar As String
        Dim dsFound As Boolean
        
        For cPos = csStart To csLen
            
            cChar = Mid(CurrencyString, cPos, 1)
            
            Select Case cChar
            Case DecimalSeparator
                If dsFound = False Then
                    rString = rString & DecimalSeparator
                    dsFound = True
                Else
                    Exit Function ' two decimal separators
                End If
            Case ThousandsSeparator
            Case Else
                If IsNumeric(cChar) Then
                    rString = rString & cChar
                Else
                    ' neither ds, ts nor digit
                End If
            End Select
        
        Next cPos
        
        If IsNumeric(rString) Then
            CurrencyToNumber = CDbl(rString)
        End If
    
    End Select
    
End Function

Sub CurrencyToNumberTEST()
    Const ExamplesList As String = "|1|abc|$12.34|-5kn|-56.78|1,005.67|34.67.56"
    Dim Examples() As String: Examples = Split(ExamplesList, "|")
    Dim n As Long
    For n = 0 To UBound(Examples)
        Cells(n + 2, "A").Value = CurrencyToNumber(Examples(n))
        Debug.Print n, CurrencyToNumber(Examples(n))
    Next n
End Sub