3

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
Improve this question
2
  • 1
    is the (2 names) part of the string?
    – Cameron Aziz
    Jul 2, 2012 at 15:15
  • 1
    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, 2012 at 18:07

2 Answers 2

Reset to default
4

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
Improve this answer
9
  • It would be a better answer and more in line with the Stack ethos if you explained WHY.
    – Julian Knight
    Jul 2, 2012 at 16:09
  • @JulianKnight better?
    – Cameron Aziz
    Jul 2, 2012 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, 2012 at 18:42
  • @CameronAziz: Nice one.
    – Julian Knight
    Jul 2, 2012 at 20:24
  • 1
    I suggest using LEN(A1) instead of 255 in all instances.
    – Excellll
    Jul 2, 2012 at 23:43
2

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .