Number format

[tleathem]

Hi there, is it possible to have a VBA code to format numbers to 2 different formats depending on the first digit, I did some basic coding 40 odd years ago, and would probably have used something like the following, If first character is 0, then use format 0000-000-000 else use format 0000-0000, not sure how or if possible to do this with VBA any help greatly appreciated. As an aside, these are phone number formats that we use in an Excel spreadsheet, some customers don't have a mobile number so we have to use a land line number. Thank you for you time

 

[AlanSidman]

Option Explicit

Sub tleathem()
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr
        If InStr(Range("A" & i), 0) = 1 Then
            Range("A" & i) = Application.Text(Range("A" & i).Value, "0000-000-000")
        Else: Range("A" & i) = Format(Range("A" & i), "####-####")
        End If
    Next i
    Application.ScreenUpdating = True

End Sub

Assumes your data is in column A starting in Rows 1

 

[tleathem]

Option Explicit

Sub tleathem()
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr
        If InStr(Range("A" & i), 0) = 1 Then
            Range("A" & i) = Application.Text(Range("A" & i).Value, "0000-000-000")
        Else: Range("A" & i) = Format(Range("A" & i), "####-####")
        End If
    Next i
    Application.ScreenUpdating = True

End Sub

Assumes your data is in column A starting in Rows 1

Thank you so much for that, I will try it when I get home this evening and let you know, once again, thank you