CODEWITHSUNDEEP
vbaexcel
TCassa
TCassa

Reputation: 145

Convert Text format to Number format

I have an Excel sheet which contains a list of numbers. Anything below 10 shows as 00 format but it has the little green arrow denoting an error, and I have to click and choose 'convert to number', as below:

Tooltip Convert To Number

How do I convert these into numbers without doing it manually? If I try to record a macro it doesn't record anything.

Upvotes: 0

Views: 1356

Answers (3)

Ivan Tokarev
Ivan Tokarev

Reputation: 101

Looks like @Skip Intro sollition does not always work with Excel 2010 and above, since i had no result on several machines with it while having no error as well.

So i made a new script, which works fine for me:

Sub convert_text_to_numbers()

Dim varCounter As Integer
Dim varCellInRange, tableRange, startingCell, endingCell As Range

Set startingCell = Cells(1, 1)
Set endingCell = Cells.Cells(Cells.SpecialCells(xlLastCell).Row, Cells.SpecialCells(xlLastCell).Column)
Set tableRange = Range(startingCell, endingCell)

varCounter = 0
For Each varCellInRange In tableRange
    If IsNumeric(varCellInRange) Then
        varCellInRange.Value = Val(varCellInRange)
        varCounter = varCounter + 1
    End If
Next

MsgBox ("Converted " & varCounter & " instances")

End Sub

Old answer:

I use a little cheat for this, can't say it's perfect, but you can try it for yourself:

 Sub fixNumberFormats()

     Columns("A:A").TextToColumns

 End Sub

Instead of Columns("A:A") you can address specific cells if needed by modifying the code a bit.

Upvotes: 2

Skip Intro
Skip Intro

Reputation: 860

This will find the last row of data, then converts any text 'numbers' in the active column.

Sub Convert_Text_Num()

Dim lngCounter As Long
Dim rCell As Range
Dim strRange As String
Dim lngCol As Long

lngCounter = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row 'Find last row of data
lngCol = ActiveCell.Column

vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)

strRange = Col_Letter & "1:" & Col_Letter & lngCounter


        For Each rCell In Range(strRange).Cells

            rCell.NumberFormat = "General"
            rCell.Value = rCell.Value

        Next rCell

MsgBox "All Done!", vbOKOnly + vbInformation, Now()

End Sub

Upvotes: 2

Pierre
Pierre

Reputation: 1046

Several possibilities. If they truely are numbers: sometimes this is enough:

range("A1:A1000").numberformat="0.00"

range("A1:A1000").value=range("A1:A1000").value 'yes it looks soooo dumb

Otherwise, put it in a vector and use cdbl() on each. You might need to use "replace" to replace the wrong decimal separator with the right one

Upvotes: 5

Related Questions