VBA Express : Excel - String To Number Converter

Excel

String To Number Converter

Ease of Use

Easy

Version tested with

2003

Submitted by:

MaximS

Description:

This simple macro will remove all non-numeric characters from cell containing string and convert it to number.

Discussion:

This code is helpful whenever we need to convert string containing both characters and numbers to number. It doesn't matter if number position is at the begining of the string (ie. 125efg = 125), in the middle of the string (ie. abc12de = 12) or at its end (ie. abc123 = 123). It will also pick up separators (ie. dot - abc15.85a = 15.85 or comma 15,01an = 15,01). Code will jump to another cell every time if string does not contain any numbers or after the number there is any other character (ie. a1aba12 = 1).

Code:

instructions for use

			Option Explicit 
 
Sub String_To_Number_Converter() 
     
    Dim x, y As String 
    Dim LastRow, i, j As Long 
     
     'This will look for last row in the column where you want to convert cells.
     'Change "A" to other column if needed
    LastRow = Range("A65536").End(xlUp).Row 
     
     
    For i = 1 To LastRow 
         
        For j = 1 To Len(Cells(i, 1)) 
             
            x = Mid(Cells(i, 1), j, 1) 
             
             'Chr(47) To Chr(58) are 0 To 9 numbers, Chr(44) is comma and
             'Chr(46) is dot
            If x > Chr(47) And x < Chr(58) Or x = Chr(44) Or x = Chr(46) Then 
                 
                y = y + x 
                 
            Else 
                 
                If y <> "" Then 
                     
Goto Jump: 
                     
                End If 
                 
            End If 
             
        Next j 
Jump: 
         
         'Change 1 to column number where you want to convert cells.
         'Ie. 2 Means column "B"
        Cells(i, 1).Value = y 
        y = "" 
    Next i 
End Sub

How to use:

1. Copy above code.
2. In Excel press Alt + F11 to enter the VBE.
3. Press Ctrl + R to show the Project Explorer.
4. Right-click desired file on left.
5. Choose Insert >> Module.
6. Paste code into the right pane.
7. Press Alt + Q to close the VBE.
8. Save workbook before any other changes.
9. Press Alt + F8, select 'String_To_Number_Converter', press Run.

Test the code:

1. From an existing workbook, save first.
2. Press Alt + F8.
3. Choose 'String_To_Number_Converter'.
Press 'Run'.

Sample File:

String To Number Converter.zip 7.66KB

Approved by lucas

This entry has been viewed 218 times.