Excel Custom Formatting Conditions

[ShankedS]

Ever since I discovered custom cell formatting, I have enjoyed using it, especially for phone numbers. However, there was one problem I ran into.



As long as I kept the phone number formatting to a base phone number, which has a predictable number of numbers. However, what about extensions? That's where I'm running into issues. Apparently Excel cell formats can contain conditionals, which is awesome. This conditional almost does everything I need it to:

[<=9999999]###-####;[<=9999999999](###) ###-####;[<=999999999999](###) ###-#### x##;

But plenty of extensions are more than 2 numbers. Some are three, others are four. I've seen extensions as many as 6 digits long. However, when I try to modify the conditional formatting I've seen, I get errors:

[<=9999999]###-####;[<=9999999999](###) ###-####;[<=999999999999](###) ###-#### x##;[<=9999999999999](###) ###-#### x###;

I'm clearly doing something wrong, but I'm not sure what. I read that past versions of Excel (2003) weren't able to handle more than 3 conditions (4 if you include the default), but that later versions (2007, 2010, 2013, etc...) could handle more than 3. If that's the case, then it's almost certainly my mistake. But, since I followed the rules I noticed in the original, I can't, for the life of me, seem to figure out what went wrong.

[xor]

Use Conditional Formatting.

[ShankedS]

Originally Posted by xor

Use Conditional Formatting.

I thought that was just for things like colors and borders and such?

[xor]

Select the cell(s) to format, Conditional Formatting, New Rule, Use a formula to determine which cells to Format. In the New Formatting Rule dialog in the field Format values where this formula is true, you write:

=$A$1>9999999999999

Press Format. In the Format cells dialog select the Number tab. Under Category select Custom and in the field Type, write "("###")" ###-#### ####. OK you out. Then in the Conditional Formatting Rules Manager press New Rule and repeat the process with next condition ($A$1>999999999999) and so on. Be aware that Excel cannot handle more than 15 digits as numbers.

[ShankedS]

Originally Posted by xor

Select the cell(s) to format, Conditional Formatting, New Rule, Use a formula to determine which cells to Format. In the New Formatting Rule dialog in the field Format values where this formula is true, you write:

=$A$1>9999999999999

Press Format. In the Format cells dialog select the Number tab. Under Category select Custom and in the field Type, write "("###")" ###-#### ####. OK you out. Then in the Conditional Formatting Rules Manager press New Rule and repeat the process with next condition ($A$1>999999999999) and so on. Be aware that Excel cannot handle more than 15 digits as numbers.

Thanks, primary issue here now is that I'm not sure how to manage priority. If I set up 5 conditions (10 digit number, no extension; 11 digit number, 1 digit extension; 12 digit number, 2 digit extension; 13 digit number, 3 digit extension; 14 digit number, 4 digit extension; ) how do I stop multiple matches from happening?

I.E. 15, 14, 13, and 12 digits are all greater than 10 digits, so a greater than 10 digits for a 1-digit extension will run into issues.

[xor]

That's why I changed your

<= smallest number, format for smallest number, <= next smallest number, format for next smallest number and so on to

> largest number, format for largest number, > next largest number, format for next largest number and so on.

If that doesn't help please specify exactly all your numbers and matching formats.

[ShankedS]

Originally Posted by xor

That's why I changed your

<= smallest number, format for smallest number, <= next smallest number, format for next smallest number and so on to

> largest number, format for largest number, > next largest number, format for next largest number and so on.

If that doesn't help please specify exactly all your numbers and matching formats.

So I tried it with the largest rule first as well as the smallest rule first, but having them all together it's still not working.

[xor]

Can't you try to upload a workbook with more examples (numbers of different size). In the picture showed in #7 I can't see the numbers, but based on your file it seems that they all have 10 digits.

[ShankedS]

Originally Posted by xor

Can't you try to upload a workbook with more examples (numbers of different size). In the picture showed in #7 I can't see the numbers, but based on your file it seems that they all have 10 digits.

Fixed that bit, did one of each length.

[xor]

I expected you to show all the number intervals and the matching formatting.

[ShankedS]

Originally Posted by xor

I expected you to show all the number intervals and the matching formatting.

Ah. Well, this should do it then.

[xor]

Don't know if this can be of any help. I have no further suggestions.

[xor]

What I wrote in #6 was wrong.
My apologies.

But even with your own logic (smallest number first) I can't get it correct.

I give it up on this one.

[NoSparks]

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.

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