+ Reply to Thread
Results 1 to 14 of 14

Formula to validate MAC Address

  1. #1
    Registered User
    Join Date
    02-24-2014
    Location
    Minnesota, MN
    MS-Off Ver
    Excel 2010
    Posts
    3

    Question Formula to validate MAC Address

    Hello,

    I am trying to create a data validation formula that will only allow the following characters, 0-9 and A-F.

    ABCD1230AD89 TRUE
    ABCD1234AD90 TRUE
    ABCD1234AD91 TRUE
    ABCD1234FD92 FALSE
    AJCD1234AD93 FALSE
    ABCD1234GH94 FALSE

    I have been trying combinations of ISNUMBER, SEARCH, LEFT, FIND, ISERROR and I get stuck for example when I say allow E, however if E isn't listed then the result is FALSE...

    Any help would be greatly appreciated.

    Thank you,
    Mindy

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to validate MAC Address

    Does it have to be the EXACT pattern:

    The first 4 characters must be UPPERCASE letters A-F followed by 4 digits from 0-9 followed by 2 UPPERCASE letters A-F followed by 2 digits from 0-9.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Formula to validate MAC Address

    @Tony: if it's a MAC Address, it is six pairs of characters, "octets", 12 characters in all. Each character can be 0 to 9, A to F.

    http://en.wikipedia.org/wiki/MAC_address

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    02-24-2014
    Location
    Minnesota, MN
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Formula to validate MAC Address

    Hi Tony, thank you for the response. There is absolutely no pattern, can be any combination of 0-9 and A-F. The letters can be upper or lower case. I would just like to check the charactes in the cell, if a character is not 0-9 or A-F then it is false. Thank you!

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Formula to validate MAC Address

    Try
    =ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefABCDEF")))

    see attached file for HEX allowed - but I have change to only support your letters

    change to the above formula for HEX allowed
    Attached Files Attached Files
    Last edited by etaf; 02-24-2014 at 07:46 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Formula to validate MAC Address

    @etaf: that is so neat. TMS

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to validate MAC Address

    Quote Originally Posted by etaf View Post
    Try
    =ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefABCDEF")))
    You can shorten that a bit since both upper and lower case are allowed.

    =ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdef")))

    it is six pairs of characters,...12 characters in all.
    Then we need to limit the string length to 12 characters, right?

    Try this...

    Create this named expression...

    Goto the Formulas tab>Define Name
    Name: String
    Refers to: ="0123456789abcdef"
    OK

    Then, set the validation...

    Allow: Custom
    Formula:

    =AND(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),String),LEN(A1)=12)

    Uncheck: Ignore Blank

    OK out

    If you test that formula in a worksheet cell it has to be array entered.

    Array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Last edited by Tony Valko; 02-24-2014 at 10:08 PM.

  8. #8
    Registered User
    Join Date
    02-24-2014
    Location
    Minnesota, MN
    MS-Off Ver
    Excel 2010
    Posts
    3

    Red face Re: Formula to validate MAC Address

    Good Morning! Everything worked beautifully! Thank you very much to everyone!

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to validate MAC Address

    You're welcome. We appreciate the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Formula to validate MAC Address

    your welcome, also learnt a little more my self from tony - thanks

  11. #11
    Registered User
    Join Date
    06-30-2014
    Location
    Austin. Texas
    MS-Off Ver
    This varies
    Posts
    3

    Re: Formula to validate MAC Address

    First time user, I hope I'm not missing any procedures prior to posting a question. The following is for 2013.

    I need help trying to configure a single column in a worksheet for students that will ONLY require them to document MAC addresses, and they must be in colon format.

    The second part to this is I'm trying to set it up to allow multiple mac entries within the same cell.

    See attached for clarification.

    TIA!

    Restrict Field Entry_Test.xlsx
    Last edited by vino831; 06-30-2014 at 05:38 PM.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to validate MAC Address

    Vino, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Registered User
    Join Date
    06-30-2014
    Location
    Austin. Texas
    MS-Off Ver
    This varies
    Posts
    3

    Re: Formula to validate MAC Address

    Thank you for the follow up!

  14. #14
    Registered User
    Join Date
    10-23-2019
    Location
    H No 173, sec 11 1/2 , karachi
    MS-Off Ver
    2016
    Posts
    1

    Re: Formula to validate MAC Address

    Thanks for your sharing !

+ 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. Validate two fields when one field you need a formula to validate
    By cmwilbur in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-29-2010, 11:32 AM
  2. Validate an email address
    By hustla7 in forum Excel General
    Replies: 2
    Last Post: 08-27-2006, 01:55 PM
  3. Validate an Email Address
    By Duncan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2006, 02:40 PM
  4. [SOLVED] Validate email address
    By Ken Valenti in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2006, 08:35 PM
  5. [SOLVED] Validate an email address
    By BethP in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2005, 07:05 PM

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