Validate South African ID Number with Excel Formula

I have written a single Excel formula to validate South African ID Numbers. I think it is complete. I would welcome input from readers on simplifying. I know there are implementations that exist in Excel but I don’t know of one that uses a single formula in a single cell.

I’ve created an Excel sheet with some made up ID numbers as examples.

These are the checks:

  • If it is not 13 digits long, returns: “Invalid Length”
  • If date digits are > 12, returns: “Invalid Month”
  • Making some assumptions about the year, if the entire date of birth is wrong, return: “Invalid Day”
  • If citizenship bit is not 0 (citizen), 1 (resident) or 2 (refugee), return: “Invalid Citizenship”
  • If the penultimate bit is not 8 or 9, return: “Invalid 2nd Last Digit”
  • Then, the bulk of the formula implements the Luhn algorithm to confirm the check bit. If invalid, returns: “Invalid Check Bit”

Assuming the ID Number is in cell A1, this is the formula from B1 to validate:

=IF(AND(ISNUMBER(INT(A1));LEN(A1)=13);IF(INT(MID(A1;3;2))<13;IF(IF(LEFT(A1;2)>RIGHT(YEAR(TODAY());2);NOT(ISERROR(DATEVALUE(CONCATENATE(1900+INT(LEFT(A1;2));"/";MID(A1;3;2);"/";MID(A1;5;2)))));NOT(ISERROR(DATEVALUE(CONCATENATE(2000+INT(LEFT(A1;2));"/";MID(A1;3;2);"/";MID(A1;5;2))))));IF(INT(MID(A1;11;1))<3;IF(OR(INT(MID(A1;12;1))=8;INT(MID(A1;12;1))=9);IF(INT(MID(A1;13;1))=10-IF(MOD(IF(LEN(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2)=7;(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;6;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;7;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1));IF(LEN(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2)=6;(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;6;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1));(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1))));10)=0;10;MOD(IF(LEN(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2)=7;(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;6;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;7;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1));IF(LEN(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2)=6;(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;6;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1));(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1))));10));"Valid";"Invalid Check Bit");"Invalid 2nd Last Digit");"Invalid Citizenship");"Invalid Day");"Invalid Month");"Invalid Length")

Minimal Ubuntu Time Machine Backup Service

I’ve been using a Time Machine backup over AFP to an openmediavault (OMV) box. I’ve got some new hardware and installed Ubuntu 16.04 instead. Alas, OMV uses a custom-rolled version of netatalk 3.x to provide AFP services, while Ubuntu (and Debian upstream) only provide 2.x.

This seemed strange to me. netatalk 3.0 was released over 5 years ago, and should have become available in Debian long ago, so I started to research why there was no netatalk 3.x package in Debian. Along the way, I bumped into a comment on a Debian bug report which mentioned that Apple is phasing out the AFP protocol in favour of SMB (note the comment at the very end of article). Ah!

Right, so, I still have no idea why netatalk 3.x is not available in Debian, but it made perfect sense to me to explore what had been done in the Samba project to implement support for Time Machine.

As luck would have it, Samba 4.8.0 was released a couple of weeks ago, and is the first version to include the Time Machine backup support. Unfortunately, because it is so new, it is not be available as an installable package in Ubuntu 16.04 or even 18.04. So, to use it, one must compile Samba from source.

This process works for Ubuntu 16.04 and I am now using it instead of OMV for Time Machine backups.

The steps below will produce a Time Machine backup service that is open and accessible to anyone on the network (ie. guest only). If you want a permissioned service, you will need to adjust the Samba global configuration and the configuration of the Time Machine share to your preferred permissioning method.

Perform the following steps as the root user.  This is easiest via interactive sudo:

sudo -i

Install pre-requisites:

apt-get install -y libreadline-dev git build-essential \
libattr1-dev libblkid-dev autoconf python-dev \
python-dnspython libacl1-dev gdb pkg-config libpopt-dev \
libldap2-dev dnsutils acl attr libbsd-dev docbook-xsl \
libcups2-dev libgnutls28-dev tracker libtracker-sparql-1.0-dev \
libpam0g-dev libavahi-client-dev libavahi-common-dev \
bison flex avahi-daemon avahi-discover avahi-utils libnss-mdns \

Download the source, configure, build and install:

cd /usr/src
tar -xzvf samba-4.8.0.tar.gz
cd samba-4.8.0
./configure --systemd-install-services --with-shared-modules=idmap_ad --enable-debug --enable-selftest --with-systemd --enable-spotlight --prefix=/opt/samba4
make install

Because we installed it to /opt/samba4, add this to the path:

echo 'export PATH=$PATH:/opt/samba4/bin:/opt/samba4/sbin' >> /etc/profile
source /etc/profile

Create the log file location:

mkdir -p /var/log/samba

Check that the version we are using includes Avahi, Spotlight and Time Machine (check that the flags returned by each of the 3 commands match):

# smbd -b | grep -i avahi
# smbd -b | grep -i spotlight
# smbd -b | grep -i fruit

Below is a minimal /opt/samba4/etc/smb.conf that has been carefully checked to ensure that parameters that are on by default or implied by others have been removed. This configuration assumes that the location you want to save Time Machine backups to is mounted at /mnt/backup.  We used a RAID 1 mirror formatted with ext4.


map to guest = Bad User
vfs objects = catia fruit streams_xattr
log file = /var/log/samba/%m
log level = 2
mdns name = mdns

# Time Machine
fruit:veto_appledouble = no
fruit:encoding = native
fruit:metadata = stream

# Security
server min protocol = SMB2

[Time Machine]
fruit:time machine = yes
path = /mnt/backup
guest only = yes
writeable = yes

Starting Samba:

# smbd -s /opt/samba4/etc/smb.conf

To perform your backup, select the “Time Machine” on your server from the “Available Disks” in Time Machine and get started.



Validating South African Cell Numbers with a Regular Expression (Regex)

These days legislation increasingly places an obligation on processors of personal information to ensure that information is accurate.  Input sanitisation and data validation are critical approaches to maintaining accuracy.

I scrounged information about active South African mobile number prefixes using information from the Wikipedia article on South African telephone numbers, a February 2016 post on the mailing list, as well as HLR validation logs.

After munging all of the info from those sources, the following regular expression – as at today – validates all cellphone numbers using known active prefixes in South Africa and excludes the rest.

I took care to try and ensure the regex is as character compact as possible while still providing for all known 3 and 4 digit prefixes.

The regex is:


An example usage (including start and ending terminators) would be:

SELECT * FROM cellnumber_listing
WHERE NOT (trim(celltelephone) ~ '^0((60[3-9]|64[0-5]|66[0-5])\d{6}|(7[1-4689]|6[1-3]|8[1-4])\d{7})$');

Or for Perl Compatible Regular Expression (PCRE):

grep -P '0((60[3-9]|64[0-5]|66[0-5])\d{6}|(7[1-4689]|6[1-3]|8[1-4])\d{7})' file.txt

If you have any comments on the above or suggestions how it could be improved, please comment!

Using Custom Domains on WordPress Multisite

Everything I read online about WordPress Multisite suggests that sites are either available at or at

If you are and dishing out sites like, that makes sense.  However, if you are me, trying to put,, and on the same WordPress install then you need to do something special through the services of a plugin, they say.

Or do you?

I followed the multisite install instructions on WordPress 4.5.2, choosing subdomain as the option (it was the only option because I was converting and existing installation).  I followed all the instructions about adding entries to .htaccess and wp-config.php.

Then, everything I read suggested I needed one of a couple of MU Domain plugins to get domains working.  That was until I saw a comment on one of those plugins suggesting plugins are no longer needed.

So, this how to do it:

  • Update your DNS for (use A records, not CNAMEs) to point to the same web server hosting your WordPress Multisite install
  • Create a new site in the network, with Site Address (URL) like “”
  • Once it is created, go to My Sites -> Network Admin -> Sites and click Edit on the site you just created
  • Change the Site Address (URL) to whatever your desired domain is eg. (I left the trailing / in place)
  • On your hosting provider or web server configuration, point to the same directory where your WordPress Multisite enabled deployment lives
  • Test by going to in your browser.

Let me know how it goes!

Ramblings on Residential RAID

For the average home NAS, Read/Write performance is not usually a critical factor.

Space efficiency is often the main reason why people with 3 or more drives decide to use something other than RAID 1 (mirroring). But, IMHO for the average – less technical – home user, anything more than RAID1 is asking for trouble.

The biggest benefit of RAID1 is simplicity. One drive mirrors the other. 4 x 4TB drives = 2 x 4TB storage in 2 x 4TB RAID 1 mirrors.

If a drive fails, replace it and the mirror will rebuild. Do it ASAP because you want the rebuild to happen before the sole remaining drive can fail.

If you want to treat the array as 1 x 8TB device instead of 2 x 4TB, you can look at RAID10 which benefits from RAID1 and stripes the two RAID1 arrays into a single device.

However, I would again suggest KISS. One of the biggest risks with a home NAS is that when a problem finally occurs, it has been AGES since you built the thing, and you don’t remember the config exactly – what goes where, what to do to rebuild etc.

         RAID1                   RAID10
     4TB   |   4TB                8TB
    RAID1  |  RAID1         RAID1     RAID1
    (4TB)  |  (4TB)         (4TB)     (4TB)
    (4TB)  |  (4TB)         (4TB)     (4TB)

Read more


SA Open Source Solutions Provider Speaking at INDIASOFT and Exhibiting at CeBIT, Germany

PRESS RELEASE: “SA Open Source Solutions Provider Speaking at INDIASOFT and Exhibiting at CeBIT, Germany”, 3 February, 2009

SA Open Source Solutions Provider Speaking at INDIASOFT and Exhibiting at CeBIT, Germany 

Durban based Open Source Solutions Provider, THUSA, has been invited to attend and speak at the INDIASOFT trade show to be held in Kolkata, India in late February 2009.  THUSA MD, Warwick Chapman, will be speaking to the Indian software development community about the opportunities available to them on the continent of Africa and in South Africa especially.

A week after INDIASOFT, THUSA will be exhibiting at the world’s largest technology trade show, CeBIT, which is held in Hannover, Germany every year.  THUSA will be participating in the National Pavilion display sponsored by the South African Department of Trade and Industry and will be exhibiting its Vulani server product.

Of their participation in the two events, Chapman says “We are receiving support from both the Indian High Commission and the dti to make the two events a reality for our small business.  Hopefully we can show both the Indian and greater international IT community that our wholly South African developed product can provide a compelling cost-effective and OSS-enabling alternative the big brands which dominate the market space.”

THUSA primarily develops the OSS-based server product called Vulani (“Open Up” in isiZulu) which is due to be launched in South Africa during April 2009 after over 3 years of active development and 5 years of Research & Development.

Vulani aims to bridge the gap between the opportunities to lower cost and improve openness provided by Open Source Software, and the complexity of deploying, maintaining and monitoring Open Source Systems.

Chapman says “Vulani lowers server software and maintenance costs for small businesses and developing nations significantly by providing a layer of consistent, reliable and simple to use management software atop the many complex Open Source Systems which are used to make up the many OSS-based solutions available today.  Vulani’s strength is in lowering the skills requirement for organisations to sustainably benefit from the many advantages of using OSS-based solutions.”

Vulani installations are already in use in other Southern African countries with servers installed in Zambia, Malawi and Rwanda.  The Malawian branch of the Management Sciences for Health, based in Boston and employing 7000 people worldwide, runs a Vulani server to provide a stack of solutions ranging from firewalls and email services through to filesharing and backup on a single Vulani server for over 100 users.  Roger Day, the service provider who selected Vulani for the site, says he chose the product for MSH because “of the versatility and flexibility of the system and the ease with which it can be supported remotely.”

Vulani pricing, while not yet finalised, is expected to be significantly lower than existing solutions and will also include no per user licensing at all.



THUSA was established in 2002 and has grown from a technical services business to include several focus areas including software development and OSS solutions. THUSA’s primary development focus is the OSS-based Vulani server product due for release in South Africa in February 2009.

More information can be found at


About Vulani

Vulani has been developed taking into account our collective experience as a Microsoft Partner, in networking consulting and in Open Source Software systems and development. Vulani provides a safe and sustainable solution to utilising Open Source Software solutions in any enterprise – drastically lowering TCO and simplifying management.

Vulani’s features provide everything you need to manage your company’s IT infrastructure inexpensively and efficiently. There are no client licenses, so expandability is easy and affordable, offering room to grow and expand without needing expensive upgrades.

Vulani’s intuitive interface allows easy, permissions-managed user access to your IT systems. It is now possible to maintain a complex infrastructure without expensive external companies.  An advanced reporting system provides full access to your organisation’s system usage, allowing you to access bandwidth figures, email statistics and network traffic which provides an unparalleled view of how your infrastructure operates.

Vulani takes the best of open source network management software and unifies it in a solid communications and networking platform; stable, well-tested and industry proven.

Vulani development is partly supported by a grant from the SPII Fund of the dti.

More information can be found at


About CeBIT

CeBIT is the world’s largest computer expo. It is held each spring on the world’s largest fairground in Hannover, Germany, and is a barometer of the state of the art in information technology.  By 2008, the CeBIT expo attendance was at 495,000 and was held from 4 March to 9 March 2008. The 2009 expo dates are 3 March to 8 March 2009. The CeBIT 2009 event will be organized by Deutsche Messe AG and is expected to surpass the previous year’s success. (Source: Wikipedia)

More information can be found at


About INDIASOFT 2009

INDIASOFT 2009 opens a plethora of opportunities for Exhibitors as well as IT Buyers globally looking for innovative outsourcing IT services & solutions including KPO, BPO, LPO, Banking, Finance, Insurance, Telecommunications, Media & Entertainment, Bio Informatics, Engineering Design, Travel, Hotel, Hospitality, Logistics, Transportation, Educational, Pharma, eGovernance, and much more under one roof.

There will be around 100 Indian IT Tier II and III companies displaying a world of IT innovations and around 200 IT buyers from 50 countries for business networking. Visit INDIASOFT 2009 for the growth of your enterprise. (Source:

More information can be found at