Eliminate data entry key strokes and mistakes in a cell - Input mask? Textlike?

[JET2011]

I want to make data entry in a cell easier so the input person does not have to enter the two constants every single time.

For instance, in column A1 the only entries will be a 9 characters length; ####AA###.

The AA never changes and the other charters are numeric, fixed length 9 character total.

Some commands researched are textlike and input mask. Input mask sounds best but in any case don't know how to use them.

Would guess the textlike formula would be like this - but how to actually use it no idea
"=TextLike(A1, "[0-9][0-9][0-9][0-9][A][A][0-9][0-9][0-9]")"

I search our forum but no hits on textlike, although an input mask might be better.

Thanks

[scott.s.fower]

if it is something that is done on a regular basis, maybe a form would be best, you could have the form put the string together

you would enter the seven numbers in the text box then have excel do something like Left(xyz,4)&"AA"&right(abc,3)

example:

Please Login or Register  to view this content.

for more info on forms see link below:

http://www.fontstuff.com/ebooks/free/fsUserForms.pdf

[scott.s.fower]

a second method would be to have the user input the digits into one cell, then have a control cell with "AA" and have a cell=based formula

eg.
colA colB
1 - AA <--your control cell
2 - 1234567 =left(A2,4)&$A$1&right(A2,3) --> will output 1234AA567
3 - 7654321 =left(A3,4)&$A$1&right(A3,3) --> will output 7654AA321
4 - 2345671 =left(A4,4)&$A$1&right(A4,3) --> will output 2345AA671
5 - 3456712 =left(A5,4)&$A$1&right(A5,3) --> will output 3456AA712

a third option would be to have sheet1 be an input screen with a button to "post" the data to sheet2, the sheet1 will clear so you can input the next set of data (this would be the simplest to program if you don't have strong VBA skills)

[JET2011]

Thanks Scott, I can't really change the users experience - international users, can't change the instructions at this point. Have you heard about the things I mentioned? The first idea seems way overkill or I just don't understand. I use mask concepts in data bases can you do this in excel.

Thanks again.

J

[scott.s.fower]

i'm not sure what mask is... so i'm not certain sorry... i will look it up and if i can figure it out then i'll let you know

[scott.s.fower]

ok, i think i understand what you're getting at

if you format the input cells as ####\A\A### it will do what you want, even if they input 1234AA567 it will comeout correctly, formatting the cells in this way is limiting because what if it should be 1234BB567, then you have to format the cell to include the BB

[JET2011]

I think we are getting there - the column will ALWAYS have the AA in this location. Thanks again, if this works don't know how to actually do it.

[scott.s.fower]

good to know, hope it works out for you

right click on the cell(s), if you highlight multiple, select format cells then follow the picture

format cells.png

[JET2011]

I'm not sure that will work. I believe that works best when only 7 characters are input and then the 8th & 9th characters are inserted nicely. That would require an explanation to international users who are accustomed to entering all 9 characters, need this to be seamless. When you enter all 9 characters and the AA is entered wrong ie 1234AZ123, it accepts the wrong letters with no argument. Another detail I failed to mention, it is technically a text field because we regularly have leading zeros and this custom format drops the zeros.

[FDibbins]

That wont work, because it is just formatting - a cosmetic change to a cell. it is not changing the actual contents of the cell. so, although it looks like 111AA111 in actually fact, the cell still only contains 111111

Probably your best bet (without using VBA) is to use the data entry column as a "helper" column where the user enters 111111, then in a 2nd column, use scott's suggested...
=left(A2,4)&$A$1&right(A2,3)
if you format the entire column as text before hand, it should accept leading 0's without a problem

edit: i have never heard of "textlike" or "input mask" as a regular formula or function in excel

[JET2011]

Just a warning to the user that the AA is wrong would be good.

[JET2011]

FDibbins & Scott - thanks for the help. I found "textlike" & "input mask" by googling, must be for other software. I have used VB code, largely cut and past from a real developer. FD, concerning your comment the actual data stored will not match as it appears, that is OK - underlying value not a concern.

Regards,

J

[FDibbins]

If you go the "helper" route, you wont need to warn them, and they wont need to enter it?

[JET2011]

I also came across this (with some nice forum assistance) which looks great, believe used with data validation. It makes sure the input is of proper length and has the desired imbedded letters. Maybe need a little guidance on how to put this into practice will do it.

[FDibbins]

Interesting approach. I played around with it, and it VERY restrictive, maybe just what you want?

to use it, highlight the range of cells you want to use, then select data validation from the data tab

from the drop-down, select "custom" and enter that formula. but make sure the A1 reference is adjusted to suite the 1st cell in your range. selct OK, and you are done.

Test it in a few cells just to make sure it is working....it will ONLY accept input in the format of 1111aa111 that is...4 numbers, 2 a's (upper or lower), 3 numbers

[JET2011]

Got it! My issue with the data validation above was could not figure out how to copy down the formatting (validation) So adding the range into the formula did the trick.

For instance:

SOLVED (too bad don't know how to mark as solved)

[JET2011]

Look like we got it simultaneously.