Excel - How do programmatically convert 'numbe

Question

I'm looking for a simple Excel VBA or formula that can convert an entire row in Excel from 'number stored as Text' to an actual Number for vlookup reasons.

Can anyone point me in the right direction?

Answer 1

I wrote a custom vlookup function that doesn't care about data formats. Put this into a module in VBA and use = VLOOK instead of = VLOOKUP

Public Function VLook(sValue As String, rDest As Range, iColNo As Integer)

' custom vlookup that's insensitive to data formats

Dim iLastRow As Long
Dim wsDest As Worksheet

Set wsDest = Sheets(rDest.Parent.Name)

iLastRow = wsDest.Range(wsDest.Cells(100000, rDest.Column).Address).End(xlUp).Row

If iLastRow < rDest.Row + rDest.Rows.Count Then
    For X = rDest.Column To rDest.Column + rDest.Columns.Count
        If wsDest.Cells(100000, X).End(xlUp).Row > iLastRow Then iLastRow = wsDest.Cells(100000, X).End(xlUp).Row
    Next X
End If

sValue = UCase(Application.Clean(Trim(sValue)))

For X = rDest.Row To iLastRow

    If UCase(Application.Clean(Trim(wsDest.Cells(X, rDest.Column)))) = sValue Then
        VLookDM = wsDest.Cells(X, rDest.Column + iColNo - 1)
        Exit For
    End If

Next X

End Function

Answer 2

The easiest way I can think of is using the built-in function =VALUE(TEXT_TO_CONVERT_TO_STRING).

Answer 3

I did find this, but does anyone have a formula as well?

Sub macro() 
    Range("F:F").Select 'specify the range which suits your purpose
    With Selection 
        Selection.NumberFormat = "General" 
        .Value = .Value 
    End With 
End Sub 

http://www.ozgrid.com/forum/showthread.php?t=64027

Answer 4

This should work if you add it before your vlookup or index/match lines:

Sheets("Sheet1").UsedRange.Value = Sheets("Sheet1").UsedRange.Value

Answer 5

Try this:

Sub ConvertToNumber()
    Application.ScreenUpdating = False
    Dim cl As Range
    For Each cl In Selection.Cells
        cl.Value = CInt(cl.Value)
    Next cl
    Application.ScreenUpdating = True
End Sub

To use it, simply select the relevant block of cells with the mouse, and then run the macro (Alt+F8 to bring up the dialogue box). It will go through each cell in the selected range and convert whatever value it holds into a number.

Answer 6

Better Approach

You should use INDEX(MATCH) instead of VLOOKUP because VLOOKUP behaves in an unpredictable manner which causes errors, such as the one you're presumably experiencing.

INDEX ( <return array> , MATCH ( <lookup value> , <lookup array> , 0) )

Using 0 as the last argument to MATCH means the match must be exact
Here is some more in-depth information on INDEX(MATCH)-ing

Further

Add zero +0 to convert a value to a number.
This can be (dangerously) extended with IFERROR() to turn non-numeric text into a zero:

=A2+0
=IFERROR(A2+0,0)

For the inverse, you can catenate an empty string &"" to force the value to be a string.

Notes

If 0 is not used as the last argument to MATCH, it will find all sorts of unexpected "matches" .. and worse, it may find a different value even when an exact match is present.

It often makes sense to do some extra work to determine if there are duplicates in the MATCH lookup column, otherwise the first value found will be returned (see example).

Help with MATCH comes from here, notably the matching logic the 3rd argument controls.