Need to Concatenate & if/replace a string of characters

So essentially I am creating a Unique ID that combines first and third letters of first name, first and third letters of last name, MM, DD, YY, and gender. (This was not my idea). All together it draws characters from 4 cells. Like this:

#	A	B	C	D	E
1	first name	last name	date of birth	gender	unique ID
2	Bo	Smith	10301972	M	BSI103072M
3	Linda	Brock	09011955		LNBO090155

So the UID in cell E2 would be the result if I used "&" and "MID" to concatenate like this:
=MID(A2,1,1)&MID(A2,3,1)&MID(B2,1,1)&MID(B2,3,1)&MID(C2,1,2)&MID(C2,3,2)&MID(C2,7,2)&D2

The problem is that I have to replace all blanks "" with a 9. So I would want the UIDs to read B9SI103072M and LNBO090159. However, my formula simply leaves out blanks.

I thought I could use the IF function to check/replace like so: =IF(MID(A2,3,1)="",9)

However I couldn't figure out how to get the formula to "put" the character, and "if/then" it at the same time. Much less do this for the whole string.

I tried: =MID(A2,1,1)IF(MID(A2,1,1)="",9)&MID(A2,3,1)IF(MID(A2,3,1)="",9)&.... but that didn't work.

Any ideas?

Try

=MID(A2,1,1)&MID(A2&REPT("9",3),3,1)&MID(B2,1,1)&MID(B2,3,1)&MID(C2,1,2)&MID(C2,3,2)&MID(C2,7,2)&D2

Everyone forgot that middle Initial not present had to be replaced with a 9.

Formula:

Please Login or Register  to view this content.