+ Reply to Thread
Results 1 to 4 of 4

Data validation on specific text to identify website addresses .com OR .co.uk

  1. #1
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    81

    Data validation on specific text to identify website addresses .com OR .co.uk

    HI I am trying to put data validation on cells in excel so it checks whether they have ".com" OR ".co.uk" i.e. they are validish web addresses. I can get it to work for one by doing this in the custom validation field

    =ISNUMBER(FIND(".com",H3))

    but I have tried variations of using OR but I can't get it to work, An example of what I have tried is this - I am not great at custom data validation so any help appreciated!

    =OR(ISNUMBER(FIND(".COM",H3),(FIND".CO.UK",H3))

    Thanks

    Jen

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,511

    Re: Data validation on specific text to identify website addresses .com OR .co.uk

    Try it like this:

    =OR(ISNUMBER(FIND(".com",LOWER(H3))),ISNUMBER(FIND(".co.uk",LOWER(H3))))

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    81

    Re: Data validation on specific text to identify website addresses .com OR .co.uk

    Brilliant this seems to have done the trick! what does the LOWER do? just out of interest!
    Thanks

    Jen

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,511

    Re: Data validation on specific text to identify website addresses .com OR .co.uk

    It ensures that H3 is in lower case, as I put .com and .co.uk in lower case - you could use SEARCH instead of FIND, and that is case-sensitive.

    Thanks for the rep, and for marking the thread as Solved.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Data validation for specific text and numbers
    By chaiyya345 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-10-2020, 09:37 PM
  2. Data Validation - If cell contains specific text then error
    By Lgonzalez91 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-22-2019, 04:02 PM
  3. Replies: 3
    Last Post: 03-30-2019, 12:48 PM
  4. [SOLVED] Data Validation Alert text linked to specific cell
    By Otto Wirke in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 11-27-2018, 10:15 PM
  5. Data Validation - Need a date or specific text to be input
    By garethmckeeveraxa in forum Excel General
    Replies: 2
    Last Post: 04-25-2016, 05:54 AM
  6. VBA to identify certain text in a data validation field
    By CLoos in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-21-2013, 05:03 PM
  7. [SOLVED] Data validation based on another cell containing specific text
    By Sph01 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-06-2013, 05:51 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1