+ Reply to Thread
Results 1 to 33 of 33

Data Validation: String of Numbers and A Single Letter at End

  1. #1
    Registered User
    Join Date
    06-26-2013
    Location
    USA
    MS-Off Ver
    Windows 7
    Posts
    45

    Data Validation: String of Numbers and A Single Letter at End

    Hello,

    I am looking to create a unique Customer ID using 1) 8 digits and an optional single letter at the end, 2) with dashes after the third and fifth digits, and 3) ensure there are no duplicates. For example, "12345678A" would be shown as "123-45-678A" with no duplicates allowed. Or, "12345678" would be shown as "123-45-678" with no duplicates allowed.

    I would like to avoid combining two cells, if possible.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,296

    Re: Data Validation: String of Numbers and A Single Letter at End

    Do you want something that will generate those numbers, or as your title says, something that will do data validation to ensure that a number typed in will conform to those rules?
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-26-2013
    Location
    USA
    MS-Off Ver
    Windows 7
    Posts
    45

    Re: Data Validation: String of Numbers and A Single Letter at End

    Data validation; to ensure that a number manually typed in conforms to those rules.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: Data Validation: String of Numbers and A Single Letter at End

    Pl see attached file. A2:a14 is validated.
    Attached Files Attached Files

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    Quote Originally Posted by b624333 View Post
    Hello,

    and an optional single letter at the end.
    Does case matter?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    Quote Originally Posted by kvsrinivasamurthy View Post
    Pl see attached file.
    Normally I don't download files but in this case I did!

    Your formula allows entries like:

    1E3-45-6.8A
    1.3-45-.08A
    1.3-45--10A

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: Data Validation: String of Numbers and A Single Letter at End

    @Tony Valko ,
    I have corrected the formula.
    For A2 ,
    Please Login or Register  to view this content.
    Last edited by kvsrinivasamurthy; 06-16-2014 at 05:08 AM.

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    That formula won't accept entries like:

    123-45-678

    Here's my offering. Assuming the letter can be any case.

    =AND(COUNT(-MID(A5,ROW(INDIRECT("1:3")),1))=3,MID(A5,4,1)="-",COUNT(-MID(A5,ROW(INDIRECT("5:6")),1))=2,MID(A5,7,1)="-",COUNT(-MID(A5,ROW(INDIRECT("8:10")),1))=3,OR(LEN(A5)=10,LEN(A5)=11),IF(LEN(A5)=11,SEARCH("~"&RIGHT(A5),"abcdefghijklmnopqrstuvwxyz"),1))

    If the letter needs to be in uppercase:

    =AND(COUNT(-MID(A5,ROW(INDIRECT("1:3")),1))=3,MID(A5,4,1)="-",COUNT(-MID(A5,ROW(INDIRECT("5:6")),1))=2,MID(A5,7,1)="-",COUNT(-MID(A5,ROW(INDIRECT("8:10")),1))=3,OR(LEN(A5)=10,LEN(A5)=11),IF(LEN(A5)=11,FIND(RIGHT(A5),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),1))

    If you test those formulas on the worksheet they must 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.

  9. #9
    Registered User
    Join Date
    06-26-2013
    Location
    USA
    MS-Off Ver
    Windows 7
    Posts
    45

    Re: Data Validation: String of Numbers and A Single Letter at End

    Tony's point is well taken. kvsrinivasamurthy's formula doesn't allow for entry of things like 123-45-678. However, Tony, I cannot seem to get your formula to work. I have never used an array formula, much less for a custom data validation formula. Could you explain this to me further?

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    Ooops!

    I completely forgot this requirement:

    with no duplicates allowed
    When I was writing the formula in post #8 I was thinking to myself: man, this would be much easier if we could use some type of regular expression pattern recognition. So, I posted about this in the MS MVP Discussion List.

    MS Excel MVP Brad Yundt came up with a good idea of using array constants that will significantly reduce the formula length but we have to use defined names as array constants are not allowed in data validation formulas.

    So, if you can answer this question:

    Does the optional letter have to be in uppercase?

    Then we can finish the formula.

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: Data Validation: String of Numbers and A Single Letter at End

    Try this for A2
    Please Login or Register  to view this content.

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    That allows entries like:

    123-45-67
    123-45-6
    123-45-

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: Data Validation: String of Numbers and A Single Letter at End

    One more trail for A2
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    06-26-2013
    Location
    USA
    MS-Off Ver
    Windows 7
    Posts
    45

    Re: Data Validation: String of Numbers and A Single Letter at End

    Yes, it would have to be uppercase. Thank you for your help.

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    Try this...

    First, create these defined names.

    Goto the Formulas tab>Define Name

    Name: Chars1
    Refers to: ={1,2,3,5,6,8,9,10}

    Name: Chars2
    Refers to: ={4,7}

    Name: Letters
    Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    OK out

    Now setup the validation.

    Let's assume you want to apply the data validation to the range A2:A10.

    Select the entire range A2:A10.

    Goto the Data tab>Data Validation
    Allow: Custom
    Formula:

    =AND(COUNTIF(A$2:A$10,A2)<2,COUNT(-MID(A2,Chars1,1))=8,MID(A2,Chars2,1)="-",FIND(MID(A2&"A",11,1),Letters),LEN(A2)<12)

    Uncheck: Ignore Blank

    OK out

    Try it out.
    Last edited by Tony Valko; 06-18-2014 at 03:49 PM.

  16. #16
    Registered User
    Join Date
    06-26-2013
    Location
    USA
    MS-Off Ver
    Windows 7
    Posts
    45

    Re: Data Validation: String of Numbers and A Single Letter at End

    That is absolutely amazing. Well done. Works perfectly. Only allows values in the format of ###-##-### or ###-##-###X, with no duplicates.

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    You're welcome. Thanks for 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.

  18. #18
    Registered User
    Join Date
    06-26-2013
    Location
    USA
    MS-Off Ver
    Windows 7
    Posts
    45

    Re: Data Validation: String of Numbers and A Single Letter at End

    There's a problem. The data validation above works great, until I save and close. If I reopen and wish to add more "Customer IDs", the data validation doesn't work (i.e. it doens't let anything get entered into the cell).

    I can't figure out why! Why would this be?

    I attached an example workbook... Try it for yourself. You can't enter anything. But when you start from scratch it works fine, until you save and close.

    HELP!
    Attached Files Attached Files

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    Hmmm...

    The file does as you say in Excel 2007 and Excel 2010.

    However, when I create the same file in Excel 2002 (*.xls file format) it works as expected, no problems whatsoever.

    Further, when I open the *.xls file in Excel 2010 it works as expected, no problems whatsoever.

    I'm completely baffled.

    Let me try to get some other folks to have a look at this and see what they can come up with.

  20. #20
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: Data Validation: String of Numbers and A Single Letter at End

    Both lower and upper case letters will be accepted.IF only upper case is required
    Please Login or Register  to view this content.

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    That allows entries like:

    123-45-6e2
    123-45-e10
    123-4e-002
    123-45-6e2A

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    Not having much luck trying to figure out what the problem is.

    The formula works, it's the application of the validation that is causing the problem. I don't know if this is some kind of bug or what's causing it.

    A couple of alternatives...

    1. Use a helper column. For example, if you want to apply the validation to the range A2:A10, array enter the formula in B2 and copy down to B10.

    Then, as the validation rule use:

    Allow: Custom
    Formula: =B2=TRUE

    You can hide the helper column if you want.

    Tested this and it does work as expected. Saving the file, closing it then reopening it and the validation still works as expected.

    2. Use an event macro. The macro runs when a change is made to the cell. It would test that the entry meets the conditions. If it does it allows the entry. If it doesn't meet the conditions then it would pop up a message box letting the user know that what they tried to enter is an invalid entry.

    Unfortunately, I'm not much of programmer so I can't help you option 2. You could start a new thread in the Excel Programming / VBA / Macros forum and include a link to this thread so folks can see what was already discussed/tried.

  23. #23
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,335

    Re: Data Validation: String of Numbers and A Single Letter at End

    @Tony Valko
    Hats Off to your patience.
    Using Helper cell or Worksheet event is an alternative. I wanted to get in normal way using CUSTOM.I have given one more trail now. Pl Check.

    Please Login or Register  to view this content.
    Last edited by kvsrinivasamurthy; 06-30-2014 at 03:41 AM.

  24. #24
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Data Validation: String of Numbers and A Single Letter at End

    I don't know why, but if you break the 8 number part of the validation out into a separate named formula:
    =COUNT(MID(Sheet1!A2,{1,2,3,4,5,6,7,8,9,10},1)+0)=8
    and then use:
    =AND(COUNTIF(A:A,A2)<2,Is8Nums,MID(A2,4,1)="-",MID(A2,7,1)="-",ISNUMBER(FIND(MID(A2&"A",11,1),Letters)),LEN(A2)<12)
    it seems to work.
    Remember what the dormouse said
    Feed your head

  25. #25
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Data Validation: String of Numbers and A Single Letter at End

    Interesting.

    Is this documented?

    Regards
    Last edited by XOR LX; 06-30-2014 at 05:25 AM.
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  26. #26
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Data Validation: String of Numbers and A Single Letter at End

    I don't know, because I don't know why it's necessary, which makes it hard to search for a documented bug (which is hard enough anyway!).

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    Quote Originally Posted by romperstomper View Post
    I don't know why, but if you break the 8 number part of the validation out into a separate named formula:
    =COUNT(MID(Sheet1!A2,{1,2,3,4,5,6,7,8,9,10},1)+0)=8
    and then use:
    =AND(COUNTIF(A:A,A2)<2,Is8Nums,MID(A2,4,1)="-",MID(A2,7,1)="-",ISNUMBER(FIND(MID(A2&"A",11,1),Letters)),LEN(A2)<12)
    it seems to work.
    Good job!

    One thing to be careful about...

    When defining Is8Nums make sure cell A2 is the active cell.

    I was kind of suspicious about the array constants but setting the validation to use my original monster formula (with a couple of tweaks) that doesn't use array constants also had the same problems in Excel 2007+.



    Man, I hate not knowing why things don't work when they should.

  28. #28
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Data Validation: String of Numbers and A Single Letter at End

    Well, you know who to ask...

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    On the few occasions when I've specifically asked "them" for comment on other issues I got none!

  30. #30
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Data Validation: String of Numbers and A Single Letter at End

    Quote Originally Posted by Tony Valko View Post
    Man, I hate not knowing why things don't work when they should.
    Agreed. This one is a bit of a mystery...

    Quote Originally Posted by romperstomper View Post
    Well, you know who to ask...
    Then go ask, my friend! Go ask!

  31. #31
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Data Validation: String of Numbers and A Single Letter at End

    Quote Originally Posted by Tony Valko View Post
    On the few occasions when I've specifically asked "them" for comment on other issues I got none!
    Well, we can but try. Although if it is a bug, I estimate the chances of it being fixed at a fairly round number...

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

    Re: Data Validation: String of Numbers and A Single Letter at End

    Quote Originally Posted by kvsrinivasamurthy View Post
    Pl Check.

    =AND(ISNUMBER(VALUE(LEFT(A2,3)&MID(A2,5,1)&MID(A2,6,1)&MID(A2,8,3))),ISERROR(FIND("e",A2)),ISERROR(FIND(".",A2)),COUNTIF($A$2:$A$2000,$A2)=1,LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))=2,IF(LEN(A2)=11,AND(CODE(RIGHT(A2,1))>64,CODE(RIGHT(A2,1))<91),LEN(A2)=10))
    Still allows entries like:

    123-45-6E1
    123-45-E01
    123-4E-001
    123-E0-001
    12E-00-001
    1E0-00-001

  33. #33
    Registered User
    Join Date
    06-26-2013
    Location
    USA
    MS-Off Ver
    Windows 7
    Posts
    45

    Re: Data Validation: String of Numbers and A Single Letter at End

    Quote Originally Posted by romperstomper View Post
    I don't know why, but if you break the 8 number part of the validation out into a separate named formula:
    =COUNT(MID(Sheet1!A2,{1,2,3,4,5,6,7,8,9,10},1)+0)=8
    and then use:
    =AND(COUNTIF(A:A,A2)<2,Is8Nums,MID(A2,4,1)="-",MID(A2,7,1)="-",ISNUMBER(FIND(MID(A2&"A",11,1),Letters)),LEN(A2)<12)
    it seems to work.
    Thank you so much! Looks like we all learned something new... I certainly did. Thank you for all your help, patience, and commitment.

+ 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] First String Character is the letter T or a number and remainder are numbers only
    By Sorjas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2014, 12:32 AM
  2. instances of a single digit in a string of numbers
    By rockchalk313 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-19-2014, 08:32 AM
  3. Replies: 2
    Last Post: 08-23-2012, 04:58 PM
  4. Data Validation - 1 letter, 2 numbers
    By RumbleBee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-19-2012, 08:16 AM
  5. Replies: 17
    Last Post: 07-16-2009, 03:12 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