View original (French)

Check the compliance of a registration

Solved
bod77 Posted messages 52 Registration date   Status Member Last intervention   -  
bod77 Posted messages 52 Registration date   Status Member Last intervention   -
Hello,

I want to check in Excel cells the compliance of license plates in the format "AA-123-CC".

I would like it so that if the cell has the correct format with the 2 "-" characters, it returns the value 1; otherwise, it returns the value 0.

Thank you for your help

Best regards.

19 answers

  2.
    bod77 Posted messages 52 Registration date   Status Member Last intervention  
     
    Additional information, the license plate number is provided to me by a service provider (a large list of several hundred plates) and I want to make sure there is no mistake like a missing letter or number.
    Also, I'm having trouble understanding the reasoning behind the LEFT function.
    0
    1. alexdu17200 Posted messages 1579 Status Member 841
       
      I don't use it often, but I just tested it in Excel, so:
      I have my cell A1 that contains "AA-123-CC", and I tell the cell next to it, =LEFT(A1,2), it returns "AA", which is the first 2 characters of cell A1.
      And the LEFTB function works the same way except that one character equals 2, so if you used LEFTB(A1,2) you'd get "AA-1", which I don't see much point in.
      0
    2. bod77 Posted messages 52 Registration date   Status Member Last intervention  
       
      What I wish, for example, is that if the person inputs the plate:
      AA-12-CC : False
      A-123-f : false
      AA-123-CC : TRUE
      0
    3. alexdu17200 Posted messages 1579 Status Member 841
       
      You have the NBCAR() function that returns the number of characters, then you do a check on this number
      MAJUSCULE() to convert all your cell content to uppercase.

      If I understand correctly, your plate must follow the format "XX-111-XX" where X is a letter and 1 is a digit, you can therefore check that =CHERCHE("-",A1) returns 3 (the position of "-" in the string) and then check that =CHERCHE("-",A1,(B4+1)) where B4 is the position of the first character "-" which is at 7.
      0
  3. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    Try it like this to test cell A1, the formula is to be incremented down the height of your data list

    = IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))/LEN("-")=2,1,0)

    You can also enforce the input, for example, highlight the range A1:A100, then Data/Data Validation/select Custom in Allow and paste this formula in Formula

    = SUM(LEN(A15)-LEN(SUBSTITUTE(A15,"-","")))/LEN("-")=2

    Then, in the Error Alert tab, you can enter a title and a message like the input is not valid, etc.

    If this solution interests you, I will look at a code to impose the hyphen after two digits and before the last two

    Cheers
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  4. Mazzaropi Posted messages 1983 Registration date   Status Contributor Last intervention   72
     
    bod77, Hello.

    "...I would like that if the cell has this correct format with the 2 "-" then it returns the value 1 otherwise the value 0. ..."

    A1 = AA-123-CC

    Try using this formula.
    B1 --> =IF(AND(MID(A1,3,1)=\"-\";MID(A1,7,1)=\"-\");1;0)

    Was this what you wanted?
    I hope I helped.
    --
    Belo Horizonte, Brasil.
    Marcílio Lobão
    0
  6. bod77 Posted messages 52 Registration date   Status Member Last intervention  
     
    Thank you Mazzaropi, that works, you’re taking a weight off my shoulders

    Thanks also to the others for the help
    0
  7. ccm81 Posted messages 11033 Status Member 2 434
     
    Hello everyone

    And if we want to test all the characters of the registration number (but excluding the prohibited letters)
    =IF(LEN(A2)=9;IF(AND(AND(CODE(MID(A2;1;1))>=65;CODE(MID(A2;1;1))<=90);AND(CODE(MID(A2;2;1))>=65;CODE(MID(A2;2;1))<=90);MID(A2;3;1)="-";AND(CODE(MID(A2;4;1))>=48;CODE(MID(A2;4;1))<=57);AND(CODE(MID(A2;5;1))>=48;CODE(MID(A2;5;1))<=57)*AND(CODE(MID(A2;6;1))>=48;CODE(MID(A2;6;1))<=57);MID(A2;7;1)="-";AND(CODE(MID(A2;8;1))>=65;CODE(MID(A2;8;1))<=90);AND(CODE(MID(A2;9;1))>=65;CODE(MID(A2;9;1))<=90));1;0);0)

    Best regards
    0
  8. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    I am coming back with my validation list proposal. In my previous suggestion, if we replace my formula with this one, it will be impossible not to respect the entry of two letters, a hyphen, a random number, a hyphen, and two letters to finish.

    =AND(SEARCH("-",A18)=3,LEN(A18)-(SEARCH("-",A18,4)-1)=3)

    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  9. ccm81 Posted messages 11033 Status Member 2 434
     
    Hi Mike

    With your last proposal, this is accepted
    $p-123-!m or 12-123-12
    Furthermore, I believe that certain letters (O, I, U) are not valid in a registration number

    Best regards
    0
  10. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    Thanks ccm, yes that's exactly it, but my formula is based on two characters and a hyphen at the start, a minimum of three characters in the center, and a hyphen and two characters to finish
    if the input is not compliant a personalized error message

    attached is an example file for those following the discussion

    https://www.cjoint.com/?DDzt5hae6UY

    --
    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  11. bod77 Posted messages 52 Registration date   Status Member Last intervention  
     
    Once again, a big thank you to everyone for your help, which has been very valuable to me.
    0
  12. bod77 Posted messages 52 Registration date   Status Member Last intervention  
     
    Hello,

    I now need to perform an Excel test on the old license plates to see if they are valid during input.

    Can you help me again?

    Thank you in advance.
    0
  13. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Sure,

    Explain what you want and then we'll see if it's possible

    --
    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  14. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    for fun with custom functions 


    Option Explicit
    '-------
    ''old plates
    Function Verifier_oldplaque(Plaque As String) As Boolean
     Dim Separe, Nbre_lettres As Byte
     Dim Reg As Object
     Dim Verif As Object
     
     Separe = Split(Plaque)
     Set Reg = CreateObject("vbscript.regexp")
     With Reg
     If Len(Separe(1)) < 3 Then
     .Pattern = "^[0-9]{1,4}[\s][A-Z]{1,2}[\s][0-9]{1,2}$"
     Else
     .Pattern = "^[0-9]{1,3}[\s][A-Z]{1,3}[\s][0-9]{1,2}$"
     End If
     Set Verif = .Execute(Plaque)
     End With
     Verifier_oldplaque = (Verif.Count = 1)
    End Function
    '------------------------------------
    'New plates
    Function Verifier_newplaque(Plaque As String) As Boolean
     Dim Reg As Object
     Dim Verif As Object
     
     Set Reg = CreateObject("vbscript.regexp")
     With Reg
     .Pattern = "^[A-Z]{2}[-][0-9]{3}[-][A-Z]{2}$"
     Set Verif = .Execute(Plaque)
     End With
     Verifier_newplaque = (Verif.Count = 1)
    End Function


    --
    Michel
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Hi,
      Eriic just informed me by PM that I, O, V are prohibited on the plates as well as "WW" and "SS"
      so the toys I proposed this morning are not valid.

      Sorry.
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Not invalid, but we can refine the result ;-)
      By the way, I was mistaken; it's the U instead of the V that is not used.
      Here's where I stand: 
      Function plaqueOK(Plaque As String) As Boolean Dim Separe, Nbre_lettres As Byte Dim Reg As Object Dim Verif As Object Set Reg = CreateObject("vbscript.regexp") With Reg If InStr(Plaque, "-") > 0 Then ' new plate ' 2 letters-3 numbers-2 letters .Pattern = "^[A-HJ-NP-TV-Z]{2}[-][0-9]{3}[-][A-HJ-NP-TV-Z]{2}$" Else ' old Plate Separe = Split(Plaque) If Len(Separe(1)) < 3 Then ' 1 to 4 numbers, 1 or 2 letters, 2 numbers .Pattern = "^[0-9]{1,4}[\s][A-HJ-NP-TV-Z]{1,2}[\s]((0[1-9])|([1-8][0-9])|(9[0-5])|(2[AB]))$" Else .Pattern = "^[0-9]{1,3}[\s][A-HJ-NP-TV-Z]{3}[\s]((0[1-9])|([1-8][0-9])|(9[0-5])|(2[AB]))$" End If End If Set Verif = .Execute(Plaque) End With plaqueOK = (Verif.Count = 1) End Function

      Checks old and new plates, valid letters, and departments from 01 to 95 (with 20, 2A, 2B accepted).

      I should mention that I did this based on Michel's work as I'm discovering regex. I'm far from being able to guarantee a correction for any anomalies found.
      https://www.cjoint.com/?DEytTaOcdtW

      Eric
      0
  15. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    To be exhaustive and avoid being accused of ostracism, please note that the old plates in the Overseas Territories end with 
    3 digits
    : 971 = Guadeloupe, 972 = Martinique, 973 = Guyana, 974 = Reunion.
    Also check what the regulations are for Mayotte, New Caledonia, Tahiti, Wallis and Futuna, Saint Pierre and Miquelon, etc.

    Thank you for us!

    --
    Retirement is great! Especially in the Antilles... :-)
    ☻ Raymond ♂
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello Raymond,

      And how many digits at the beginning, and how many letters possible?
      I had inquired. If you know what the underlined part means:
      The departments and collectivités 971 to 978 use a system identical to that used in mainland France with a maximum of nine characters, the first two digits of the department number increasingly being superimposed to reduce the maximum number of characters to eight.
      What should we understand? 97?
      Moreover, they talk about a max of 9 characters while in mainland France it's 8. I found that strange... Too vague all this.
      The problem is that if the rules are not clear and exhaustive, many valid plates will be declared false.
      For the other islands, there are too many exceptions.

      Eric
      0
    2. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      https://www.cjoint.com/?DEAfNIpQQyP

      It was indeed my way of signaling that this "compliance test" was far from covering all scenarios. Like Word's spell and grammar checker, it's an interesting aid, but it doesn't cover 100% of situations.

      Best regards.
      0
    3. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      I would have never imagined that...
      Integrated into a slightly more complete version (see below)
      eric
      0
  16. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    HELLO?


    Eric,
    Regarding the DOMs, maybe add an "OR" with 97[1_8]
    For the TOMs, the system is different: generally numbers followed by 1 or 2 letters (P for Polynesia)

    but the numbering of the old plates is actually complicated with p+q+n +1 exceptions and some tax niches ;o)
    the source:
    https://fr.wikipedia.org/wiki/Plaque_d%27immatriculation_fran%C3%A7aise

    we should therefore know where the applicant wishes to limit their control

    Have a nice Sunday everyone

    --
    Michel
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hi Michel,

      Okay, I'll wait for it to settle a bit before adding. For the DOMs, we'll do it like we did with the mopeds: we'll fall into ostracism.
      And if we want to be thorough, I found an interesting site: http://plaque.free.fr/f_f.html :-)
      Eric
      0
  17. bod77 Posted messages 52 Registration date   Status Member Last intervention  
     
    Hello

    The test must therefore be done in Excel but with an IF formula and not through a macro (sorry) based on the old license plates.
    I need to check that the format is correct =>
    example: 3711 LL 77 or 3711 LLL 77 or 371 LL 974 for example. If it's okay, it should return the value 1, otherwise 0.
    Also, if 371 L 99 is returned, for example, it's incorrect.

    I hope I was clear enough. Thank you for your help again.
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      3711 LLL 77

      are you sure?
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      And why not by macro? An allergy?
      4 examples, one of which is false, are not enough; you need a comprehensive list of valid cases.

      And using if() statements might make it an unbearable formula that's impossible to modify if needed.

      eric

      edit: regarding formulas, you received suggestions from ccm81 and mike. It's up to you to complete them on the same principle.
      0
  18. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    New version a bit more complete, adding the DOM-TOM and mopeds.
    https://www.cjoint.com/?DEAn6eyLKZO

    eric

    --
    By continually trying, we eventually succeed.
    So the more it fails, the more chances we have that it works. (the Shadoks)
    In addition to the thanks (yes, yes, it’s done!!!), remember to mark as resolved. Thank you
    0
  19. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    ... and I deliberately did not mention the vehicles of the State Services, military vehicles, cars with diplomatic plates (embassies), and temporary plates (in temporary transit)!

    --
    It's nice, retirement! Especially in the Caribbean ... :-)
    ☻ Raymond ♂
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      You did well, I don't think it will ever be there unless there's a specific need.
      By the way, CDs and other TU and TT are under the jurisdiction of the prefecture for allocation and do not require special processing for the control.
      eric
      0
  20. bod77 Posted messages 52 Registration date   Status Member Last intervention  
     
    My boss doesn't want any macros, and that's fine with me since I have never made any. Moreover, we have to send the file, and the recipients don't have the same version of Excel.
    On my side, I will stick to the old plates; the cylinders don't interest us.

    Format retained =>
    1234 AB 01
    123 ABC 01
    124 ABC 2A
    12 AB 01
    1 A 01
    1234 AB 971
    123 ABC 972

    123 ABC 00
    123-ABC-01
    123 ABC 96
    123 AB 1
    1234 ABC 01
    123 AI 01
    1234 AO 01
    1234 UB 01
    1234 ABC 01
    AB 123 AB
    123 AB 979
    1234 ABC 971
    0