CODEWITHSUNDEEP
excelformula
missmouse
missmouse

Reputation: 31

Generating usernames with specific characters in Excel

I am trying to create an Excel formula to generate usernames based on first and last name with additional characters at the end. I want the username to be the first initial of the first name, with up to seven letters of the last name, and then "_", two randomly generated numbers, and "_ex". For example:

Robin Carthright --> rcarthri_42_ex

John Patterson --> jpatters_75_ex

So far I have been able to do the first part of the formula for generating the first initial and last name, but can't figure out how to add the extra characters in the Excel formula.

Here is what I have now:

=LEFT(PROPER(LEFT(A1)&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))),8)

Upvotes: 0

Views: 382

Answers (1)

Solar Mike
Solar Mike

Reputation: 8415

Well, here is my take on it:

enter image description here

Formula:

LEFT(A2,1)&MID(A2,FIND(" ",A2,1)+1,7)&"_"&RANDBETWEEN(10,99)&"_ex"

You can change lower and upper in the randbetween().

Edit for lowercase only:

LOWER(LEFT(A2,1)&MID(A2,FIND(" ",A2,1)+1,7))&"_"&RANDBETWEEN(10,99)&"_ex"

Upvotes: 1

Related Questions