Skip to content
TheChatArchiveThe Chat Archive
Claudeclaude-opus-4-20250514-thinking-16k· AnthropicHugging Face LMArena Human Preference 140K

Write me a vba script to convert strings into digits

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with DeepSeekdeepseek-r1-0528’s answer.
  1. User

    Write me a vba script to convert strings into digits

  2. Claude Assistant

    Here's a VBA script with several functions to convert strings into digits in different ways:

    Option Explicit
    
    ' Function 1: Convert numeric string to number (handles decimals and negatives)
    Function StringToNumber(strInput As String) As Variant
        Dim cleanedStr As String
        
        ' Remove spaces and common formatting characters
        cleanedStr = Replace(strInput, " ", "")
        cleanedStr = Replace(cleanedStr, "$", "")
        cleanedStr = Replace(cleanedStr, ",", "")
        
        ' Check if it's a valid number
        If IsNumeric(cleanedStr) Then
            StringToNumber = CDbl(cleanedStr)
        Else
            StringToNumber = "Not a valid number"
        End If
    End Function
    
    ' Function 2: Extract only digits from a string
    Function ExtractDigits(strInput As String) As String
        Dim i As Integer
        Dim result As String
        
        result = ""
        
        For i = 1 To Len(strInput)
            If IsNumeric(Mid(strInput, i, 1)) Then
                result = result & Mid(strInput, i, 1)
            End If
        Next i
        
        ExtractDigits = result
    End Function
    
    ' Function 3: Convert each character to its ASCII code
    Function StringToASCII(strInput As String) As String
        Dim i As Integer
        Dim result As String
        
        result = ""
        
        For i = 1 To Len(strInput)
            If i > 1 Then result = result & ", "
            result = result & Asc(Mid(strInput, i, 1))
        Next i
        
        StringToASCII = result
    End Function
    
    ' Function 4: Convert spelled-out numbers to digits (basic version)
    Function WordsToNumbers(strInput As String) As Variant
        Dim dict As Object
        Dim words() As String
        Dim result As Long
        Dim word As Variant
        
        Set dict = CreateObject("Scripting.Dictionary")
        
        ' Create dictionary of number words
        dict.Add "zero", 0
        dict.Add "one", 1
        dict.Add "two", 2
        dict.Add "three", 3
        dict.Add "four", 4
        dict.Add "five", 5
        dict.Add "six", 6
        dict.Add "seven", 7
        dict.Add "eight", 8
        dict.Add "nine", 9
        dict.Add "ten", 10
        dict.Add "eleven", 11
        dict.Add "twelve", 12
        dict.Add "thirteen", 13
        dict.Add "fourteen", 14
        dict.Add "fifteen", 15
        dict.Add "sixteen", 16
        dict.Add "seventeen", 17
        dict.Add "eighteen", 18
        dict.Add "nineteen", 19
        dict.Add "twenty", 20
        dict.Add "thirty", 30
        dict.Add "forty", 40
        dict.Add "fifty", 50
        dict.Add "sixty", 60
        dict.Add "seventy", 70
        dict.Add "eighty", 80
        dict.Add "ninety", 90
        dict.Add "hundred", 100
        dict.Add "thousand", 1000
        
        ' Convert to lowercase and split into words
        words = Split(LCase(strInput), " ")
        
        result = 0
        Dim temp As Long
        temp = 0
        
        ' Simple parsing (works for basic cases)
        For Each word In words
            If dict.Exists(word) Then
                If word = "hundred" Then
                    temp = temp * 100
                ElseIf word = "thousand" Then
                    result = result + temp * 1000
                    temp = 0
                Else
                    temp = temp + dict(word)
                End If
            End If
        Next word
        
        result = result + temp
        WordsToNumbers = result
    End Function
    
    ' Example usage subroutine
    Sub TestConversions()
        Dim testString As String
        
        ' Test different conversion methods
        testString = "123.45"
        Debug.Print "StringToNumber('" & testString & "') = " & StringToNumber(testString)
        
        testString = "$1,234.56"
        Debug.Print "StringToNumber('" & testString & "') = " & StringToNumber(testString)
        
        testString = "ABC123DEF456"
        Debug.Print "ExtractDigits('" & testString & "') = " & ExtractDigits(testString)
        
        testString = "Hello"
        Debug.Print "StringToASCII('" & testString & "') = " & StringToASCII(testString)
        
        testString = "twenty three"
        Debug.Print "WordsToNumbers('" & testString & "') = " & WordsToNumbers(testString)
    End Sub
    
    ' Function to process a range of cells in Excel
    Sub ConvertRangeToNumbers()
        Dim cell As Range
        Dim selectedRange As Range
        
        ' Get the selected range
        Set selectedRange = Selection
        
        ' Process each cell
        For Each cell In selectedRange
            If Not IsEmpty(cell.Value) Then
                ' Convert and place result in the cell to the right
                cell.Offset(0, 1).Value = StringToNumber(cell.Value)
            End If
        Next cell
    End Sub
    

    This script includes:

    1. StringToNumber: Converts numeric strings (including those with formatting like "$1,234.56") to actual numbers
    2. ExtractDigits: Pulls out only the numeric digits from a mixed string
    3. StringToASCII: Converts each character to its ASCII code value
    4. WordsToNumbers: Basic conversion of spelled-out numbers to digits
    5. ConvertRangeToNumbers: A utility to process selected cells in Excel

    To use this in Excel:

    1. Press Alt + F11 to open the VBA editor
    2. Insert a new module (Insert > Module)
    3. Copy and paste the code
    4. Run TestConversions to see examples in the Immediate Window
    5. Or select cells in Excel and run ConvertRangeToNumbers to convert them

    The script handles various common scenarios for string-to-digit conversion. You can modify or extend any of these functions based on your specific needs.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.