LinkBack URL
About LinkBacks
I would like to be able to highlight cells that do not match (000) 000-0000 format. Is there a way to do this?
Please, and thank you!!!
I would like to be able to highlight cells that do not match (000) 000-0000 format. Is there a way to do this?
Please, and thank you!!!
is this a format that you have applied to a cell to display a normal number in that format
OR
are you actually entering the text
(000) 000-0000
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
I have a report that calls a list from my NAV database. I have some typical conditional formatting rules applied to highlight blanks, and I'd love to be able to highlight cells that don't meet data entry standards. I want the phone number highlighted if the format of the phone number isn't in this format (xxx) xxx-xxxx. For example if the phone number is entered as 425-555-5044, it would be highlighted because it should have been entered as (425) 555-5044.
you could test for characters
assuming you select the column with the numbers in (say column A)
=AND( LEFT(A1,1)="(", MID(A1,5,1)=")", MID(A1,6,1)=" ",MID(A1,10,1)="-")
that will test all the characters for your format ( ) -
BUT not if they put alpha characters in ie
(425) A55-504B
Thanks etaf,
I threw that in as a conditional formatting string, and nothing is happening. I changed the column numbers and played around with it a lot, but cannot seem to get it to work. Any advice, things to try?
Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
I think I've got a file attached. It's just a list of Grocery Stores, and I want to see all phone numbers that are not in this format (425) 555-5555, to get highlighted.
The list is generated from a database call, so when the user opens the workbook, the list refreshes. I'd love to be able to have a conditional formatting rule that will check the phone format, and if it's not in this format (425) 555-5555, highlight the cell.
Thanks for taking a look at it!!
Paul
create a rule
highlight column G
then use
=AND(G1<>"",NOT(AND( LEFT(G1,1)="(", MID(G1,5,1)=")", MID(G1,6,1)=" ",MID(G1,10,1)="-")))
that will also hightlight the title -
otherwise you need to select the range as G2:G????
and change all the G1 to G2 in the formula
Thank you immensely. It's working!!!!!
Small bug from etaf's in this case:
(253) 299-5002
if 1 (or more) digit after the "-" is missing, like (253) 299-500, or (253) 299-
maybe 1 more condition for LEN must be added.
Quang PT
How might that look?
Something like this:
=OR(SUMPRODUCT(--(MID(G2,{1,5,6,10},1)={"(",")"," ","-"}))<4,LEN(G2)<>14)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks