Excel experts; join firstname + lastname to make a username?

Scam

Scam

Scam

Soldato
Joined
20 Oct 2002
Posts
18,988
Location
London
Thought this might as well go here...

If I have column A with first name (say; John) and column B with last name (say; Smith) I want to create a username for them which would be jsmith. Can I automate this in Excel? :confused:

Would be nice if I can force to lowercase too... :o

Thanks!
 
I would use =LOWER(LEFT(A1,1)&B1)

Left bit takes the first characters from the left on cell A1 (in this case just one character) and the & combines cells. Lower makes it lowercase.

I don't use excel much but it is incredibly logical in its formula stuff and came to this. there is probably a better way
 
You want the "concatenate" function.

=LOWER(CONCATENATE(A1, ".", B1))

I believe will sort you out.

:Edit: Oh, that would be for "first.last", rather than "firstinit.lastname" which is what you asked for. I'm not entirely sure how you'd do that but there will be a way. Probably isn't great practice though as initials are ambiguous so unless you're adding a random number to the end or using another unique identifier then I'd make sure you have the full name.
 
Just a thought, and you may have this covered, but bear in mind that Jackie Smith and John Smith will both end up with the username jsmith. You may need to check that the username is already taken first, and if so, generate an alternative.
 
Thanks Bug One and Avenged7Fold! Next question, how come when I drag down from the bottom-right corner it duplicates the answer from my first row down, rather than amending the formula for each row? I'm also having the problem with a formula that takes values from another sheet;
='Sheet 1'!B11
Click to expand...
:confused:

nightwish said:
Just a thought, and you may have this covered, but bear in mind that Jackie Smith and John Smith will both end up with the username jsmith. You may need to check that the username is already taken first, and if so, generate an alternative.
Click to expand...
Yup this will be an issue.

Basically what I'm doing is creating a nice looking template to give to our partners to fill in employees names/details, then on my second sheet taking the essential values to create a comma delimited CSV that we can hand over to our vendor for mass-import. Kinda fun :)
 
What problem are you having with that other sheet formula?

I dont think you need/want the ' there so i guess =Sheet1!B11

As for the drag down issue, that is odd because my excel drags it down fine
 
Perhaps do the formula then drag down, rather than inputting in the names

I.e you can't drag the formula down as the formula is complete, type the formula in but remove the names, then drag the 'blank' cell down
 
Consider:
- Not all countries do firstname and lastname in the same way we do.
- Your formula might produce words or phrases that aren't PG rated.
- You might get duplicates.
 
Delvis said:
Perhaps do the formula then drag down, rather than inputting in the names
Click to expand...
Doesn't work :(

Avenged7Fold said:
What problem are you having with that other sheet formula?

I dont think you need/want the ' there so i guess =Sheet1!B11

As for the drag down issue, that is odd because my excel drags it down fine
Click to expand...
The ''s are there because I've named my sheet something (but called it Sheet 1 on here because it contains my company name :))

I'm sure I used to be able to take any formula that referenced another cell, and drag it down to make that formula work with other cells :confused: It's definitely not working.

Mind you, if I don't know how many rows there are going to be on the first sheet -- how can I make that formula work? If I duplicate the formula for 20 rows, but then it ends up with 25 rows on the first sheet -- I won't get those last 5 rows. Hmn.. :o

EDIT: Got it working. Had a "calculate sheets" set to manual rather than automatically. Yay.

But my last question stands.. how do I apply the formula on the second sheet for however-many rows there are on the first?
 
Last edited:
Zefan said:
You want the "concatenate" function.

=LOWER(CONCATENATE(A1, ".", B1))

I believe will sort you out.

:Edit: Oh, that would be for "first.last", rather than "firstinit.lastname" which is what you asked for. I'm not entirely sure how you'd do that but there will be a way. Probably isn't great practice though as initials are ambiguous so unless you're adding a random number to the end or using another unique identifier then I'd make sure you have the full name.
Click to expand...

Don't need Concatenate an ampersand (&) does the same :)
 
Scam said:
Doesn't work :(

The ''s are there because I've named my sheet something (but called it Sheet 1 on here because it contains my company name :))

I'm sure I used to be able to take any formula that referenced another cell, and drag it down to make that formula work with other cells :confused: It's definitely not working.

Mind you, if I don't know how many rows there are going to be on the first sheet -- how can I make that formula work? If I duplicate the formula for 20 rows, but then it ends up with 25 rows on the first sheet -- I won't get those last 5 rows. Hmn.. :o
Click to expand...

Try highlighting all the cells and then using CTRL+D, this will copy down the formula in the top cell (the working formula)

Might have some success with that :)


Also for your other issue just drag down to row 1000 or something like that and it'll fill in the details as they're added. Otherwise you'd need to convert your dataset to a "Table" and then the formula *should* auto copy down.
 
Marvt74 said:
Try highlighting all the cells and then using CTRL+D, this will copy down the formula in the top cell (the working formula)

Might have some success with that :)


Also for your other issue just drag down to row 1000 or something like that and it'll fill in the details as they're added. Otherwise you'd need to convert your dataset to a "Table" and then the formula *should* auto copy down.
Click to expand...
Ooh it didn't like ctrl+d :p Crash-fail!

I'll stick with just dragging down the rows for now. This is all WIP anyway :)
 
You must log in or register to reply here.
Back
Top Bottom