CODEWITHSUNDEEP
vbaexcelformatting
mxpm
mxpm

Reputation: 3

Convert Number stored as Text to Number in column

I have a column of mixed data in my Excel sheet where the numbers are formatted as "Text".

Some examples:

I need to convert all the numeric values in the column to the format "Number" so I can further process the data.

The following throws an error 400 without any further explanation:

Sheet1.Range("A2","A50000").Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
End With

I don't how many rows with values my column will contain so I had to make the range fairly big.

Upvotes: 0

Views: 32133

Answers (3)

Mahesh S
Mahesh S

Reputation: 21

''Convert text to Number with ZERO Digits and Number convert ZERO Digits  

Sub ZERO_DIGIT()
 On Error Resume Next
 Dim rSelection As Range
 Set rSelection = rSelection
 rSelection.Select
 With Selection
  Selection.NumberFormat = "General"
  .Value = .Value
 End With
 rSelection.Select
  Selection.NumberFormat = "0"
 Set rSelection = Nothing
End Sub

''Convert text to Number with TWO Digits and Number convert TWO Digits  

Sub TWO_DIGIT()
 On Error Resume Next
 Dim rSelection As Range
 Set rSelection = rSelection
 rSelection.Select
 With Selection
  Selection.NumberFormat = "General"
  .Value = .Value
 End With
 rSelection.Select
  Selection.NumberFormat = "0.00"
 Set rSelection = Nothing
End Sub

''Convert text to Number with SIX Digits and Number convert SIX Digits  


Sub SIX_DIGIT()
 On Error Resume Next
 Dim rSelection As Range
 Set rSelection = rSelection
 rSelection.Select
 With Selection
  Selection.NumberFormat = "General"
  .Value = .Value
 End With
 rSelection.Select
  Selection.NumberFormat = "0.000000"
 Set rSelection = Nothing
End Sub

Upvotes: -1

user10807631
user10807631

Reputation: 91

I have noticed many try to answer this problem with some sort of "Number formatting" such as found in the Home Ribbon. I think that the concern lies in the extraction of data from a source and having the little green arrow in the cell with a "caution" pop-up when hovering over the cell. This cannot be fixed with number formatting and has most likely been tried by the person asking for assistance. I was one of them.

With ActiveSheet.Select
    Range("A2:A10000").Select
        For Each xCell In Selection
        xCell.Value = CDec(xCell.Value)
    Next xCell
End With

Mabbutt, Dan. "Convert Text to Number in Excel." ThoughtCo, Jun. 14, 2018, thoughtco.com/convert-text-to-number-in-excel-3424223.

Upvotes: 9

Gary's Student
Gary's Student

Reputation: 96753

Avoid trying to Select both a worksheet and a range in the same statement (in fact, you can avoid Select completely):

Sub ytrewq()
    With Sheet1.Range("A2", "A50000")
        .NumberFormat = "General"
        .Value = .Value
    End With
End Sub

seems to work just fine!

Upvotes: 5

Related Questions