Write me a vba script to convert strings into digits
- User
Write me a vba script to convert strings into digits
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 SubThis script includes:
- StringToNumber: Converts numeric strings (including those with formatting like "$1,234.56") to actual numbers
- ExtractDigits: Pulls out only the numeric digits from a mixed string
- StringToASCII: Converts each character to its ASCII code value
- WordsToNumbers: Basic conversion of spelled-out numbers to digits
- ConvertRangeToNumbers: A utility to process selected cells in Excel
To use this in Excel:
- Press
Alt + F11to open the VBA editor - Insert a new module (
Insert > Module) - Copy and paste the code
- Run
TestConversionsto see examples in the Immediate Window - Or select cells in Excel and run
ConvertRangeToNumbersto 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.