Clever Geek Handbook

Excel - How to programmatically convert "number stored as text" to number?

I am looking for a simple Excel VBA or formula that can convert an entire string in Excel from "number stored as text" to actual number for "Number to vlookup" arguments.

Can anyone point me in the right direction?

+1


source to share


5 answers


The best approach

You should use INDEX(MATCH)

instead VLOOKUP

because it VLOOKUP

behaves in an unpredictable way that causes errors, such as the one you are supposedly experiencing.

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

INDEX MATCH example

Used 0

as the last argument for MATCH

means that the match must be exact
There is more information about INDEX(MATCH)

-ing here

Further

Add zero +0

to convert the value to a number.
This can be (dangerously) extended with IFERROR()

to turn non-numeric text to zero:



=A2+0


=IFERROR(A2+0,0)

For inversion, you can bind an empty string &""

to force the value to be a string.

Notes

If 0

not used as the last argument for MATCH

, it will find all sorts of unexpected "matches" .. and worse, it can find another value, even if an exact match is present.

It is often useful to do some extra work to determine if there are duplicates in the search column MATCH

, otherwise the first value found will be found (see example).

Help with MATCH

comes from here , specifically the matching logic controlled by the third argument.

+2


source


I found this, but does anyone have a formula?

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

0


source


Try the following:

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 corresponding block of cells with your mouse and then run the macro (Alt + F8 to open the dialog box). It will go through every cell in the selected range and convert any value it holds to a number.

0


source


I wrote a custom vlookup function that doesn't care about data formats. Put this in 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 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
0


source


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

0


source






More articles:


All Articles