Loop Convert text to number while avoiding cells with leading zero-CodePudding

SAP exports make all numbers into text I need to keep cells with leading zero as text while converting the rest if numeric text to number.

Sub ConvertUsingLoop()
For Each r In Sheets("Loop&CSng").UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r) Then
r.Value = CSng(r.Value)
r.NumberFormat = "General"
End If
Next
End Sub

I'm trying incorporate Left(Str, 1) = "0" to exclude cells with leading zero. Thank you for your input.

CodePudding user response：

Perhaps just add your Left$ check to the If:

If IsNumeric(r.Value)  And Left$(r.Value, 1) <> "0" Then

CodePudding user response：

This is the final code that I reached:

Sub Text2Number()
    On Error GoTo EH

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

Set Rng = ActiveSheet.UsedRange

Rng.Cells(1, 1).Select

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        If Rng.Cells(i, j) <> "" Then
            Union(Selection, Rng.Cells(i, j)).Select
        End If
    Next j
Next i
For Each c In Rng.Cells
    If IsNumeric(c.Value) And Left$(c.Value, 1) <> "0" Then
        c.NumberFormat = "General"
     c.Value = c.Value
    End If
Next
CleanUp:
    On Error Resume Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
Exit Sub
EH:
    ' Do error handling
    Resume CleanUp
End Sub