#1
|
|||
|
|||
Excel Custom Formatting Conditions
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: Code:
[<=9999999]###-####;[<=9999999999](###) ###-####;[<=999999999999](###) ###-#### x##; Code:
[<=9999999]###-####;[<=9999999999](###) ###-####;[<=999999999999](###) ###-#### x##;[<=9999999999999](###) ###-#### x###; |
#2
|
|||
|
|||
Use Conditional Formatting.
|
#3
|
|||
|
|||
I thought that was just for things like colors and borders and such? |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Quote:
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
Quote:
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. |
#8
|
|||
|
|||
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.
|
#9
|
|||
|
|||
Fixed that bit, did one of each length.
|
#10
|
|||
|
|||
I expected you to show all the number intervals and the matching formatting.
|
#11
|
|||
|
|||
Ah. Well, this should do it then.
|
#12
|
|||
|
|||
Don't know if this can be of any help. I have no further suggestions.
|
#13
|
|||
|
|||
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. |
#14
|
|||
|
|||
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 |