Input masks for Excel 2007

[ItIsHardToProgram]

Hello all, I am wondering if there is a possibility to do an input mask in excel that will return the whole mask as a value.

i.e. the cell would show like this: _______-_-_-_

The user would have to enter full 7 digit, and the 3 other digit in order for the cell to update.

The cells would return the following in a string value:
"______-_-_-_"

I have seen nothing that seemingly works, all I have seen was for time and it returns the time as a number, without the collon.

Thanks you for your help!

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

Excel does not have input masks as a feature.

Please explain your requirement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

You could enter as a number with a custom format 0###-#-#-#
or, use a formula to convert your number into a string in the correct format
=TEXT(A1,"0###-#-#-#")

Gavin

 

[ItIsHardToProgram]

Alright >.<

The excel spreadsheet is used as a frontend to enter data into a database, if it does not support input mask, it definatly won't work out.

I understand a excel spreadsheet isnt designed for database management, but still, I was hoping I could have a mask, that wouldn't let users enter an invalid number, i.e. 200800-1-E-1, since there should be 7 digit in the first segment.

Technicaly, if the mask would return 20080001E1, it wouldnt be a problem since I could then reformat the data in the transition from excel to access.

So basicaly, anything that forces the user to enter the exact number of digit required, besides a "on_change" event, since I despise those, would be very appreciated.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Gavona]

Data Validation: Allow any number between 0000000 and 9999999,
Format the column (I'll assume column A):
0###-#-#-#
Then maybe actually before transfer to your database create a helper column:
=TEXT(A1,"0###-#-#-#")
Copy to values, delete original column

Gavin

 

[SkipVought]

Why not a Data > Validation like...
[tt]
=AND(ISNUMBER(VALUE(LEFT(H2,7))),MID(H2,8,1)="-",MID(H2,10,1)="-",MID(H2,12,1)="-")
[/tt]
where h2 is the DV Cell?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

MMmm...

My message did not go through.

So, basicaly, I do not want a validation as I can't trust the users with validation of the data, they will simply ponder and wonder why its not working, waisting valuable working time.

I really want a mask that will not let them enter anything but what I require them to enter.

The outcome of the mask is not really important considering that I can just reformat everything given they entered the right amount of character.

I can't use 2 different columns for this purpouse as I do not want to reformat this perticular worksheet.

Thanks for your help!

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.