Under African Skies!

I recently had to create a list of about 200 usernames. Doing that by hand/manually will have taken me some time. I searched on the net and the help files of both excel and openoffice calc.

Here are the fomula’s I used,

=LOWER(CONCATENATE(MID(A3;1;5);MID(B3;1;2)))

and

=LEFT(A3,5)&LEFT(B3,2)

The first is rather difficult and took me some time to figure out,
the “lower” is used to change the text to all lower case characters,
then comes “CONCATENATE” with the specified fields, A3 and B3,
after that I used the “MID” function to specify the beginning character ‘;1″ and the amount of characters to follow “;5″

That formula took the first 5 characters from field A3 and the first 2 characters from field B3. Joining them together with no spaces in between.

This formula worked for me in OpenOffice 2.3

The second formula worked both in Excel 2002 and OpenOffice 2.3

To explain the second formula,
the “left function was used to start reading the field from the left,
using field A3 and using the first 5 charaters,
the ampersand (&) is used to join the two fields A3 and B3,
the goes for the second field B3, using the first 2 characters.

I then used the followign formula to create the users home directories,

=”/home/”&C3&”",

this prefixed the current text in field C3 with the value “/home/”

Then I used this data sheet to import into webmin to bulk create new users. There are some more data needed to do the bulk users creation from webmin, which I didn’t mention here. Check the webmin documention for more info on that.