Generating Random String from Text in Cells

We receive a spreadsheet of at least 60 people on a daily basis that contains the first name and last name of a person, each in its own cell. We have to manually generate IDs for all of these people. The requirements for these IDs are:

• Must be preceded with "g4a"
• Must not be longer than 8 characters in total
• Must be random characters from the first and last name.

An example would be if the person's name is John Doe their ID could be g4aeodjo (or any random characters found in their first and last name).

Is there formula that can just generate these random IDs with the above requirements?

Thank you in advance!!

=LOWER("g4a"&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1)&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1)&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1)&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1)&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1))

Assuming the first name is in A1 and last name is in B1

Originally Posted by XOR LX

So there is no restriction on repetition of characters (not given in your requirements)? Cases where the password consists of "g4a" followed by five identical characters (from the relevant name) are perfectly acceptable?

Well, there can be identical characters but only the amount that is contained in the combined first name and last name. So, in John Doe there are 2 O's, so O can be used once or two times, but not 3 since there are only 2 O's in both the first and last name. I hope that makes sense!

Originally Posted by yudlugar

=LOWER("g4a"&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1)&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1)&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1)&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1)&MID(CONCATENATE(A1,B1),RANDBETWEEN(1,LEN(CONCATENATE(A1,B1))),1))

Assuming the first name is in A1 and last name is in B1

Just tried this and it did the trick! Thank you!

It will allow you to use each character in the name more than once...

yudlugar, can the formula you provided be refined any further? Is there a way to limit the amount of repetition of the letters? Sometimes I'm getting IDs that are the same letter nearly all the way to the end. As I've explained to XOR LX, the repetition is allowed but only in the same amount of times the letter appears in the combined first and last name of each person.

Thank you.

To get use each character only once I can't get it in a single cell formula but I think this works:
A1: first name
B1: last name
C1: =LOWER("g4a"&MID(CONCATENATE(A1,B1),RANK(D1,D1:Y1),1)&MID(CONCATENATE(A1,B1),RANK(E1,D1:Y1),1)&MID(CONCATENATE(A1,B1),RANK(F1,D1:Y1),1)&MID(CONCATENATE(A1,B1),RANK(G1,D1:Y1),1)&MID(CONCATENATE(A1,B1),RANK(H1,D1:Y1),1))
D1: =IF(COLUMN(A1)<LEN(CONCATENATE($A1,$B1)),RAND(),"")
Then copy D1 across the row to whatever is a sensible limit for maximum characters in a name (say to BA1...)

Thank you for the ID Generator spreadsheet! This will help a lot. Going to start using it today.