Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to generate a username using a single name field in excel which has the person's first name and last name. I'm open to what the username would be, as long as its intuitive for the user. The name fields can be tricky, as the data looks like this:

Albert Abongo (2 names)
Stephen Michael Essuah Ackah (4 names)
Alhaji Iddrisu Abdul-KArim (3 names)

I guess my ideal usernames for these people would be

aabongo
sackah
aabdul-karim
share|improve this question
    
is the (2 names) part of the string? –  Cameron Aziz Jul 2 '12 at 15:15
    
Do you (or might you) have any users with the same name? If so, any solution needs to have a built-in strategy for avoiding duplicate usernames. –  Excellll Jul 2 '12 at 18:07

2 Answers 2

up vote 4 down vote accepted

Try using the following (with the input data in A1):

=LOWER(LEFT(A1,1)&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))))

  • LOWER: makes all output lowercase
  • LEFT(A1,1): pulls the data, starting from the left, for 1 character long.
  • & links the two parts together
  • TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(LEN(A1)))
    • the SUBSTITUTE(A1," ",REPT(" ",LEN(A1))) function counts the length of the original string and places the same number of spaces where every space exists
    • then the (RIGHT(text,LEN(A1)) pulls, starting from the right, for the length of the original string
    • TRIM removes the leading spaces
share|improve this answer
    
It would be a better answer and more in line with the Stack ethos if you explained WHY. –  Julian Knight Jul 2 '12 at 16:09
    
@JulianKnight better? –  Cameron Aziz Jul 2 '12 at 17:31
    
If any of the names have trailing spaces, you will need to replace each instance of A1 in the formula with TRIM(A1). –  Excellll Jul 2 '12 at 18:42
    
@CameronAziz: Nice one. –  Julian Knight Jul 2 '12 at 20:24
1  
I suggest using LEN(A1) instead of 255 in all instances. –  Excellll Jul 2 '12 at 23:43

Just to add to the clever answer from @CameronAziz, if you want to avoid duplicate usernames, you can add another column to the right of those usernames (in column B in this example), and fill down this formula:

=B1&IF(COUNTIF($B$1:B1,B1)>1,COUNTIF($B$1:B1,B1),"")

This will append a number to the right of the username if it is a duplicate of any previous usernames.

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.