So I am creating new usernames for users in Excel. My format will look be AL11smitJ. The AL is the state the user resides in, the 11 is the Office location number, then I take the first four characters of the last name, and the first character of the first name. Here is the function I use:



=LOWER(CONCATENATE(J75,RIGHT(E75,2),LEFT(C75,4),LE FT(A75,1)))

My problem is that I need to lengthen last names that are less than four characters with underscores. I have a column that calculates the number of underscores needed to make the last name four characters long, but have no idea were to go from there.

Don't know if it works on mac but you will get the idea

EDIT corrected formula missing parenthesis

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

It looks like this taps out the max number of arguments Excel 2011 can handle. However, I see how you added the underscores. I will post the winning function when I get it done.

Maybe you can replace concatenate with the "&"
=lower(J75&right(.....)&....

( if it works on MAC)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Got it!! Since I don't care what the spreadsheet looks like when I am done I used some helper columns. Thanks Peco for the help, your REPT suggestion was exactly what I was looking for. I just applied it differently.