dang cell format

  • Thread starter Thread starter Deckert
  • Start date Start date
D

Deckert

I'm trying to set an Excel cell format like I can do in Access to prevent
input errors.
Cell input is 1234-123-1234-123, that's to say, non-sequential numbering but
using 4 numbers dash, then 3 numbers dash, 4 numbers dash, then 3 numbers.
If the entry is wrong, go to halt/stop.

What hasn't worked;
1. conditional format
2. Format, cells, 'custom'
I've tried ####-###-####-### under custom but it fails because it
allows more then 4 numbers at the very start.
I "can" get, 123456-789-1234-234 The last 3 sequences work but
not the 1st sequence.
Format set as ####-###-####-### , then input 1234-123-1234-123
states format is wrong whether I input dashes
or not.

3. (####-###-####-###)
4. '####-###-####-###'
5. "####-###-####-###"
6. =exact(####-###-####-###)
7. =exact"####-###-####-###"
8. =exact####-###-####-###
9. =exact, ####-###-####-###
10. (####)-(###)-(####)-(###)

Any help here would be greatly appreciated.........Dan
 
Hi Deckert,

Try the following formula in Conditional Formatting. I checks the length of the string and that the dashes are in the right places. It does not check for digits/letters, though.

=((MID(A1,5,1)="-")*(MID(A1,9,1)="-")*(MID(A1,14,1)="-")*(LEN(A1)=17))=1

HTH
Anders Silven
 
CORRECTION!

Conditional Formatting in my previous post should be Data Validation.

Anders Silven

"Anders S" <[email protected]> skrev i meddelandet Hi Deckert,

Try the following formula in Conditional Formatting. I checks the length of the string and that the dashes are in the right places. It does not check for digits/letters, though.

=((MID(A1,5,1)="-")*(MID(A1,9,1)="-")*(MID(A1,14,1)="-")*(LEN(A1)=17))=1

HTH
Anders Silven
 
Deckert,

I think Anders solution would work well with Data Validation. I've changed
his expression slightly. Select the cells, then Data - Validation - Custom.
You should be able to paste it right from here:

=AND((MID(A1,5,1)="-"),(MID(A1,9,1)="-"),(MID(A1,14,1)="-"),(LEN(A1)=17))

For this to work, A1 must be the active (white) cell in your selection when
you do Data - Validation. With Data Validation, you can supply an
appropriate user-friendly error message, and a prompt that appears when the
cell has been selected.
 
I whole-heartedly thank you both for your reply & assistance.
I was unable to get Anders to work as needed but Earls' did perform as
needed.

As you noted, it does not allow for verification of #'s vs. text but that
won't be a problem. Thank you both again!

Dan
 
Back
Top