Excel Custom Formatting Conditions

NoSparks · #14 05-03-2018, 10:21 PM

For what it's worth...
I'd use VBA and the Worksheet_Change event in the sheet module.

This should remove everything but the numbers that are typed into the cell.
Count how many numbers you're left with and format them.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Telnum As String
    
'limit to single cell
If Target.Count > 1 Then Exit Sub
'limit to in column B
If Target.Column = 2 And Target.Row > 1 Then
    'ok to deal with this
    Telnum = OnlyNums(Target.Value2)
    Select Case Len(Telnum)
        Case Is < 10
            'do nothing leave as is
            Telnum = Target.Value2
        Case Is = 10
            Telnum = "(" & Left(Telnum, 3) & ") " & Mid(Telnum, 4, 3) & "-" & Mid(Telnum, 7)
        Case Is > 10
            Telnum = "(" & Left(Telnum, 3) & ") " & Mid(Telnum, 4, 3) & "-" & Mid(Telnum, 7, 4) & " x" & Mid(Telnum, 11)
    End Select
    Target.NumberFormat = "@"
    Application.EnableEvents = False
    Target.Value = Telnum
    Application.EnableEvents = True
End If
    
End Sub


Function OnlyNums(sWord As String)
    Dim sChar As String
    Dim x As Integer
    Dim sTemp As String

    sTemp = ""
    For x = 1 To Len(sWord)
        sChar = Mid(sWord, x, 1)
        If Asc(sChar) >= 48 And _
          Asc(sChar) <= 57 Then
            sTemp = sTemp & sChar
        End If
    Next
    OnlyNums = Val(sTemp)
End Function

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditional Formatting if certain cell meet certain conditions	bbutl027	Excel	1	11-25-2016 12:52 AM
Looping through word and writing to excel when certain conditions are met	Irene H	Word VBA	2	07-10-2016 05:59 PM
Excel Formula for Multiple Conditions & Results	nowco	Excel	4	03-16-2016 11:24 AM
Excel-Save Custom Formatting	CarlaK	Excel	1	10-18-2013 05:05 PM
Embedded Excel Graphs - Custom Formatting	Cody	Drawing and Graphics	0	07-02-2010 02:30 PM