Easiest way to convert number stored as text to number in Excel VBA

Question

I trying to convert this text into numbers:

I have tried different formulas but Excel is not recognizing the format. However, using the code below it converts only the first three numbers and removes the rest.

Sub ConvertTextNumberToNumber()
For Each WS In Sheets
    On Error Resume Next
    For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = Val(r.Value)
    Next r
Next WS
End Sub

The result looks like this

Does anyone have an ease fix for this without removing any numbers?

Something does not seem to be right, because if the numbers were really stored as text, then this line would have failed: `If IsNumeric(r) Then r.Value = Val(r.Value)`. Therefore, it seems that the real issue is not that the values in the cells were text but something to do with the format of the cell, probably formatted as text. — EEM, Nov 27 '15 at 16:54
@OlaOkland, remember to select an answer if someone had posted one which you used and worked. — Jean-Pierre Oosthuizen, Dec 01 '15 at 06:04

R3uK · Answer 1 · 2015-11-27T18:11:09.993

10

Easiest way to convert a number stored as text, is to multiply it by 1,
So try this If IsNumeric(r) Then r.Value = (r.Value)*1
Or you can copy paste with a multiplication by 1 and this works too! ;)

Sub ConvertTextNumberToNumber()
For Each WS In Sheets
    On Error Resume Next
    For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = (r.Value)*1
        ''Or test the values in the next column on the right
        'If IsNumeric(r) Then r.Offset(0,1).Value = (r.Value)*1
    Next r
Next WS
End Sub

edited Nov 27 '15 at 18:11

answered Nov 27 '15 at 07:29

R3uK

14,417
7
43
77

Yes, I would test your data by multiplying the text value in the next cell 1. Example: in Cell B1, try the formula: =A1 * 1. – PaulG Nov 27 '15 at 10:31
`If IsNumeric(r) Then r.Offset(0,1).Value = (r.Value)*1` normally, you'd have to post a question and not ask one in comments! ;) – R3uK Nov 27 '15 at 10:51
1

I wasn't asking a question in the comments. I can confirming that what you said was correct. And to test this would work correctly you can test in a formula adjacent to the data you want to convert before coding any VBA. ;) – PaulG Nov 27 '15 at 11:00
@PaulG : My bad, I didn't get that! ;) I edited my answer to reflect your suggestion! ;) – R3uK Nov 27 '15 at 18:12
This does not work for me on Excel 2013, Windows 7. Stays as number stored as text. – Aug 27 '18 at 19:02
@Kos : Check the format of your cells, I'll be they are in text ;) Or add `r.NumberFormat = "# ##0.00"` (inside the numeric test) – R3uK Mar 26 '19 at 15:16

score 2 · Accepted Answer · answered Nov 27 '15 at 16:21

Providing this answer in relation with OP statement:

I have tried different formulas but Excel is not recognizing the format.

There is no need to use VBA for that operation.

Use this formula if your decimal character is a period [.]

=SUM( SUBSTITUTE( SUBSTITUTE( B2, " ", "" ), ",", "." ) )

Or this one if your decimal character is a colon [,]

=SUM( SUBSTITUTE( B2, " ", "" ) )

score 0 · Answer 3 · answered Nov 27 '15 at 07:56

Provided your data is always using a "space" as a thousand separator and a "comma" as a decimal point, you can use the following(Modified from your original script).

Sub ConvertTextNumberToNumber()
    For Each WS In Sheets
        On Error Resume Next
        For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)

            'For testing purposes I like to see the cell which is selected
            r.Select
            'Setting the Cell Number Format
            'for more or less decimals just add/remove 0 after the . 
            r.NumberFormat = "0.00"
            'Assigning the Cell value to a String
            String1 = r.Value
            'Replacing the spaces with no space
            String1 = Replace(String1, " ", "")
            'Replacing the comma with a point
            String1 = Replace(String1, ",", ".")
            'Putting the new value into the cell
            r.Value = String1
        Next r
    Next WS
End Sub

Easiest way to convert number stored as text to number in Excel VBA

3 Answers3