Home | Blogs | rmaclean's blog

South African ID Number Checker in Excel version 2

Thu, 08/26/2010 - 10:37 |  Robert MacLean
Want this as an app for your smartphone? Click here

A long time ago I built a simple Excel spread sheet which worked out if an ID number was valid or not. Since then I have had a bunch of feedback on it, most about how it works. However a week ago Riaan contacted me and pointed out a bug in it so I took this as an opportunity to rebuild it.

Not only does the new version check the validity of the ID number, it tells you where the person was born, gender and birthdate.

image

Something else that I wanted to do was clean up the calculations. So now they have been moved to their own (non-hidden) tab and are fully documented. So if you are implementing your own checker you can clearly see exactly what is going on and how it all comes together.

image

Finally for those who wish to do a lot of checking, the third sheet of the Excel spread sheet contains that functionality where the checks are inline so that you can apply them to multiple ID numbers.

image

I want to extend a massive thanks to Riaan Pretorius, not only for pointing out the bug but also running the new version through it’s paces and finding some issues in it. The fact this one is much better is owed to him, I just typed the code Open-mouthed smile

You can download the Excel file below!

AttachmentSize
South African ID Number Checker v2.1.zip27.42 KB
Tags: Something Different, Tools & Apps

Thanks Rob! Very well done. Hope you doing well...

Submitted by Dave Coates on Thu, 10/27/2011 - 08:18.

Hi Robert,

Sorry to be the bearer of bad news, but there seems to be another bug. Your tool's analysis results wrongly assert that I was born in SA.

Can you recommend an ID validator? All the links I've tried don't seem to work.

Cheers
Patrick

Submitted by Patrick on Thu, 08/11/2011 - 14:43.

Double checked and there is no bug in there. To work out if someone is born in South Africa is very simple - digit 11 in the ID number. If it is zero you are South African, if it is one you aren't. You can read more on how it works at: http://www.sadev.co.za/content/what-south-african-id-number-made

If you have anything else in digit 11 I would love to hear about it, else I would say either home affairs have screwed up your ID number OR they have changed the meaning without publically updating anyone.

Submitted by Robert MacLean on Thu, 08/11/2011 - 18:09.

DEAR ROBERT

IS THERE ANYWAY YOU CAN PUT IN THE PERSONS NAME AND REQUEST THEIR ID NUMBER?

THANKS

Submitted by JEANNETTE on Mon, 06/27/2011 - 15:24.
Yes, go to home affairs or the police - they have systems that can do that.
Submitted by Robert MacLean on Fri, 07/01/2011 - 09:24.

Hi, Robert

Firstly, thanks for creating such a great tool, and also for outlining the process involved in validating an ID number.

Because I reckon that your ID Checker is such a valuable tool, I would like to request a favour. Would it be possible for you to convert your current Checker into a format that will be supported on BlackBerry phones, especially the 9800 Torch model (Software v.6)?

The reason I ask is because I work in the police, and every so often I come across some shady characters that ought to have their IDs checked. Unfortunately, the SAPS infrastructure is not always able (or even functioning) to do the necessary ID checks, so having such a powerful tool on my phone would just make things a LOT easier.

[I've tried opening your current file on my BB, but for some reason both the 'Valid' and 'Born in' fields display '#VALUE!', and both the 'Gender' and 'Birth Date' fields display incorrect results - the 'Gender' result is the opposite of what it should be, and the 'Birth Date' is incorrect by quite a number of years, months, and days.]

Thank you kindly in advance for any assistance you might be able to offer in this regard.

Cheers,

Martin

Submitted by Martin on Tue, 05/24/2011 - 21:32.

Hi Robert,
I can't open the file, says it's invalid. (Win 7, 64-bit)

Please assist,

Mike

Submitted by Visitor on Fri, 01/28/2011 - 10:53.
Try Right Click -> Save As on the link. Once downloaded make sure you open it in a newer version of Office, I have 2010 so that is what it is tested against. If you have an older version let me know and I will post a backwards compatible one.
Submitted by Robert MacLean on Mon, 01/31/2011 - 09:22.

Hey Robert, I tried to download the file 2 or three time but I always get a broken zip file...
any ideas?

Submitted by Visitor on Mon, 01/17/2011 - 13:48.
I've seen some other people with similar issues and the causes come from 1) Proxy servers - speak to your network admin for assistance 2) Bad anti-virus software can corupt it. Maybe exclude this file from being scanned automatically. 3) Try open in Windows or using 7Zip, those are the two programs I use for zip files. If you using other apps (like old versions of WinRAR or WinZip) you may have problems.
Submitted by Robert MacLean on Tue, 04/05/2011 - 10:37.

Your ID checker is still wrong. I put in mine and it established that I was male. If the middle 4 numbers starts with 0-4, the person is female, and 5-9, the person is male.

Submitted by Visitor on Tue, 09/28/2010 - 12:14.
Well spotted, that bug slipped through the testing :( I've fixed it and updated it.
Submitted by Robert MacLean on Wed, 09/29/2010 - 08:34.

Hi the 7th to 10th digits is under 5000 if you're a female, and the 8th - 10th is the numbered boy/girl that has been registered on that day

Submitted by Visitor on Wed, 11/10/2010 - 11:27.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account associated with the e-mail address you provide, it will be used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • Syntax highlight code surrounded by the <pre class="brush: lang">...</pre> tags, where lang is one of the following language brushes: as3, applescript, bash, csharp, coldfusion, cpp, css, delphi, diff, erlang, groovy, jscript, java, javafx, perl, php, plain, powershell, python, ruby, sass, scala, sql, vb, xml.

More information about formatting options

Twitter
Loading my tweets...
Contact Me | RSS Feed | Back to top


© SADev.co.za, SADev and the content here is copyright 2001-2012 Robert MacLean unless otherwise stated. Content reuse license: Creative Commons Attribution 2.5 South Africa License. Disclose Policy: Disclosure. Disclaimer: The views expressed here reflect the views of the authors alone, and do not necessarily reflect the views of any of their organizations, my customers and clients, employers, sponsors, or contributors. In particular, the views expressed here do not necessarily reflect those of the BBD (my employer) or Microsoft. All information and downloads are available without warranty and any use of such information or downloads is at your own risk. Use common sense. If you don't like what is written then do not read it.