Re: Limiting text entries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

And here is the formula for column 2:
=AND(LEN(B1)=6,ISNUMBER(RIGHT(B1,5)*1),ISTEXT(LEFT(B1,1)),CODE(B1)<91)

Four statements in the AND function, separated by commas check if
1) Entry is 6 characters long
2) Last 5 characters evaluate as number (has to be multiplied by 1,
otherwise Excel regards it as text)
3) Left character is text
4) Left character is uppercase (ASCII < 92)

Cheers,

Joerg Mochikun


"Joerg Mochikun" <no@xxxxxxxxxxxxx> wrote in message
news:gfu0jg$h30$1@xxxxxxxxxxxxxxxxxxxxxxxx
As a start for column 1:

For A1 apply the custom validation formula
=MID(A1,LEN(A1)-1,1)=","

This will allow only entries where the second from last character is a
comma.
You can use the AND function to combine more criteria in your formula and
make it as sophisticated as you like.

Joerg



"Help4me" <Help4me@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7D1CB948-00AD-48A3-9BCA-CDFDC9B2D940@xxxxxxxxxxxxxxxx
I have two columns in which I would like to limit the way text is input:

1) Lastname,First initial with no spaces (i.e., Smith,R)
2) One capital letter followed by five digits (i.e., D12345)

I do not have any ideas for column 1.

I can use the Limited Text validation feature for column 2, but that only
limits the number of characters, not the type of characters input.

Thank you so much.




.

Relevant Pages

  • Re: Excel e-mail list to netscape
    ... If you have other characters interferring, you can use Replace to remove them. ... If you have unwanted commas at the end, ... Use Ctrl+End to see where Excel thinks the data ends. ... with a space) the macro would help, and link to how to install and use a macro can be found at the top of the join.htm page ...
    (microsoft.public.excel.misc)
  • Re: File Editting
    ... than 254 characters because they do fit in a text field for the table. ... program still delimits on the commas. ... Dim strDIR ...
    (microsoft.public.scripting.vbscript)
  • Re: My Find and Replace methods are not working
    ... one or more spaces and one or more commas. ... the second case [A-Z]where it is just looking for one or more ... characters in the range A to Z and one or more spaces. ... > Doug Robbins - Word MVP wrote: ...
    (microsoft.public.word.vba.beginners)
  • preserving blank space padding when using fgetcsv
    ... I'm using fgetcsv to read a comma delimited file. ... the actual text in that column is only 5 characters long, ... When all of the length checks are finished I remove the commas and then ... print out the finished product: ...
    (php.general)
  • preserving blank space padding when using fgetcsv
    ... I'm using fgetcsv to read a comma delimited file. ... the actual text in that column is only 5 characters long, ... When all of the length checks are finished I remove the commas and then ...
    (php.general)