Results 1 to 4 of 4
  1. #1
    kid_flow
    Guest

    I want a cell to only allow a specific text string (Excel 97)

    Hi,

    I am familiar with various data validations in Excel. I can't seem to find one (or know the custom formula) to set a cell to only allow a specific text string (ex "abc") and nothing else.

    I've tried putting my text in quotes in the custom formula field to no avail. Is there a ISLIKE fn or something analogous?

    Any ideas?

    Thanx in advance.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I want a cell to only allow a specific text string (Excel 97)

    If you always want the cell to contain that string, then the easy thing is to put that string in the cell, lock the cell, and protect the worksheet.

    If you want to display an error message if the user enters something other than "abc" into cell G2, then you can use data validation with this custom formula:

    [rpre]
    =G2="abc"
    [/pre]

    If you want it to be case insensitive, then you could use:

    <pre>=LOWER(G2)="abc"
    </pre>


    You could also use the List form of validation to put a dropdown list in the cell that would only allow the user to select the values you want.

    You could also write a Worksheet Change event procedure to detect when the cell has changed and do whatever you want.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: I want a cell to only allow a specific text string (Excel 97)

    If you are familiar with the Data Validation facility, you could try setting it to Allow, List and confine your list Source to a single cell containing the value (which could be hidden if necessary)

    Andrew C

  4. #4
    kid_flow
    Guest

    Re: I want a cell to only allow a specific text string (Excel 97)

    Thanx Much,

    These suggestions are exactly what I've been looking for. I'm confident with this I can proceed.

    This forum (in my opinion) is hands down the best of it's kind for any Office Q's. I've been singing praises to all of my friends.

    Cheers

    K-Flow.

    P.S. Legare, I haven't yet implemented your VBA solution to my 'MAKEBLANK' posting. I will be sure to post my results when I do.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •