Create 'Fake Identity Generator'-style email addresses and passwords in Excel

Last week I needed to compile a list of several hundred fake customer names with fake (but verifiable) email addresses and passwords to test a client's eCommerce site.

Fake data generators

At that time, I knew of only two online identity generators: Fake Identity Generator (FIG) and GenerateData.com (GD).

By accepting three user-specified parameters (country, gender, age range) FIG outputs a lot of nice fake data, including disposable (but accessible) YopMail email addresses and relatively easy-to-remember passwords. However FIG doesn't provide free bulk identity generation so I had to generate and copy-paste identities one at a time – a very slow process – along with manually creating accounts using these fake identities at the client site (I had no access to the client content management system so I couldn't bulk register the fake identities).

On the other hand, GD allows bulk generation of identities (up to 100 at a time) for free (and with more parameters other than country, gender and age range). You'll have to specify which data to generate though:

Below are the outputs of the two. I find FIG-generated email and passwords easier on the eyes and more useful (YopMail addresses all have their own temporary inboxes in case you need to read an email confirmation). In contrast, GD-generated emails are really fake (apparently using a Latin randomizer) and don't relate to the names. And the GD-generated passwords are in ALL CAPS and are not as easy to remember as the passwords generated by FIG:

FIG
First Name	Last Name	eMail	Password
Daisy	Kohut	fykohut24@yopmail.com	y107119f5y
Philipppa	Kellner	hdphilipppa3@yopmail.com	d122102h7d
Elena	Amis	bgamis6@yopmail.com	g28943b1g
Nicole	Nickel	cxnicole23@yopmail.com	x42067c2x
Sylvie	Rigg	efrigg5@yopmail.com	f03823e4f
Jesus	Carrico	idcarrico3@yopmail.com	d509106i8d
Jerry	Ricker	hnjerry13@yopmail.com	n22728h7n
Lynda	Lowry	dglowry6@yopmail.com	g240102d3g
Alissa	Parkison	hralissa17@yopmail.com	r24678h7r
Elena	Linn	emlinn12@yopmail.com	m98596e4m
GD
First Name	Last Name	eMail	Password
Kelly	Bray	Fusce.aliquam@non.co.uk	QDO96EQG8PS
Ashely	Estrada	hymenaeos.Mauris@risusvariusorci.org	GIZ93AQL6ZG
Adele	Duncan	id@SeddictumProin.co.uk	NYB57YNH0VP
Jeanette	Mcintyre	eget@mattisornarelectus.co.uk	ZEO25FYY6ZU
Forrest	Cummings	erat.Etiam@ligulaAeneangravida.edu	DUM70NGC5RI
James	Gates	magna@Etiam.org	MDR06WXR4UC
James	Mccullough	fermentum.risus.at@arcu.com	OXV46LKQ7DY
Alvin	Rodriquez	nec@condimentumDonec.ca	DIW07UYO3SG
Tamara	Henderson	In.tincidunt.congue@fames.ca	FPZ19DCP7FK
Destiny	Jimenez	Donec.sollicitudin@commodoauctor.com	MDL85KPD9NP

Here are the password patterns:

FIG: lxxxxxxlxl = lowercase letter followed by 6 random digits [zero included] followed by a lower case letter, a single random digit, and ending in the same letter as the beginning.

GD: LLLxxLLLxLL = three uppercase letters, 2 random digits, another 3 uppercase letters, a single digit, and 2 uppercase letters.

In addition, the GD email addresses don't appear to have a consistent letter case and length pattern while the FIG email addresses have: ll(firstname/lastname)xx@yopmail.com = 2 random lowercase letters + either first name or last name + random number from 0 to 99 + "@yopmail.com"

Creating YopMail email addresses and GID-style passwords in Excel would save me time instead of having to generate hundreds of fake identities one at a time in FIG just to get names, emails and passwords (copying and pasting could lead to human errors like missing passwords).

So, a quarter of the way into the assignment, I decided to find a quicker way of getting FIG-style email addresses and passwords if I already have a list of first and last names (which are relatively easy to get from many alternative sources). In this case, I just bulk-generate the names in GD and then create the YopMail addresses and passwords in Excel on the fly.

Enter Excel

I'm no Excel expert, but Google is our friend. Here's what I needed to achieve using only formulas and functions (I don't code):

1. Generate random digits, specifying the range (for example, randomly pick a digit from 0 to 9)
2. Generate random lowercase letters (from a to z);
3. Randomly choose between first name and last name – which means combining the column letter of either the first name or surname (randomly) with the current row number; and using the INDIRECT function to have the resulting letter+number combination be treated as a cell address;
4. Remove spaces for multiple first and last names; and
5. Have all of these in lowercase.

We have these entries in row 1 of our sample spreadsheet:
Column A = First Name
Column B = Last Name

Column C = Formula to generate email address with the pattern ll(firstname/lastname)xx@yopmail.com

Column D = Formula to generate password with the pattern lxxxxxxlxl
Column E = Formula to generate a lowercase letter to begin and end the password with

Formula for email address (column C):

=LOWER(CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&SUBSTITUTE(INDIRECT(CHAR(RANDBETWEEN(65,66))&ROW())," ","")&RANDBETWEEN(0,99)&"@yopmail.com")

Breaking this down, we have:

1. Two instances of  CHAR(RANDBETWEEN(97,122) to randomly generate two lowercase letters from a (ASCII code 97) to z (ASCII code 122); followed by
2. An INDIRECT function statement that would give us the contents of the cell address made up of either column 'A' (ASCII code 65) or 'B' (ASCII code 66) [CHAR(RANDBETWEEN(65,66)], randomly picked, plus the current row number using the ROW() function (the parentheses are left empty to default to the row number the formula is on). This INDIRECT statement is wrapped in a SUBSTITUTE statement that replaces spaces with no spaces [SUBSTITUTE(INDIRECT statement," ","")]; followed by
3. A random number from 0 to 99 [RANDBETWEEN(0,99)]; followed by
4. The string "@yopmail.com"; and
5. Everything wrapped by a LOWER() function so the names will be in lowercase.

Formula for password (column D):

=E2&RANDBETWEEN(0,9)&RANDBETWEEN(0,9)&RANDBETWEEN(0,9)&RANDBETWEEN(0,9)&RANDBETWEEN(0,9)&RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(97,122))&RANDBETWEEN(0,9)&E2

This begins with:

1. The contents of the cell in column E [=CHAR(RANDBETWEEN(97,122))], which is merely a random lowercase from a to z; followed by
2. Six instances of RANDBETWEEN(0,9) to generate six random digits; followed by
3. A single random lowercase letter from 'a' to 'z' [CHAR(RANDBETWEEN(97,122)]; followed by
4. A single random digit from 0 to 9 [RANDBETWEEN(0,9)]; and
5. A repeat of the contents of column E (if only I knew of a way to repeat the random initial letter in the end there would have been no need for column E).

The ampersand characters ('&') are the glue that combines (concatenates) them together.

With these formulas in place, we end up with something like this:

The FIG-style addresses and passwords under the GD sample names would then look like:

First Name	Last Name	eMail	Password
Kelly	Bray	hhbray55@yopmail.com	i459333f3i
Ashely	Estrada	xsashely9@yopmail.com	v549901h2v
Adele	Duncan	ahduncan72@yopmail.com	u815492b6u
Jeanette	Mcintyre	eumcintyre38@yopmail.com	i152585j4i
Forrest	Cummings	rwcummings4@yopmail.com	j093138e2j
James	Gates	kegates35@yopmail.com	g976437q8g
James	Mccullough	jwjames92@yopmail.com	d984813k8d
Alvin	Rodriquez	mjrodriquez43@yopmail.com	d646990u9d
Tamara	Henderson	smhenderson21@yopmail.com	r992070y5r
Destiny	Jimenez	mcdestiny63@yopmail.com	y618348p0y

Note however that, because of the randomizer function, the email address and password cells tend to recalculate and change every time you press the enter key in any cell in your sheet (or if you double-click a column header to adjust column width). 'Recalculate' is usually the F9 key. If you already like the emails and passwords you see, copy and paste them as values to another sheet to fix them in place.