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

[Scam]

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?

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

Thanks!

 

[Mark M]

Not sure on lowercase without a macro but to combine the cells just use =A1&B1

 

[Bug One]

https://stackoverflow.com/questions...haracter-of-a-cell-with-another-cell-in-excel

 

[Avenged7Fold]

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

 

[Narj]

=LOWER(LEFT(A1,1)&B1)


??

EDIT: beaten

 

[Zefan]

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.

 

[nightwish]

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.

 

[Delvis]

If you asked me earlier I'd have butchered the work one

 

[Scam]

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

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.

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

 

[Avenged7Fold]

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

 

[Delvis]

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

 

[Randomface74]

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.

 

[peterwalkley]

Also consider what happens when the name you generate already exists on the mail server, either as the primary mailbox name or an alias.

 

[Scam]

Perhaps do the formula then drag down, rather than inputting in the names

Doesn't work

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

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 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..

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?

 

[Martynt74]

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.

Don't need Concatenate an ampersand (&) does the same

 

[Martynt74]

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 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..

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.

 

[Zefan]

Don't need Concatenate an ampersand (&) does the same

Much easier

 

[Scam]

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.

Ooh it didn't like ctrl+d Crash-fail!

I'll stick with just dragging down the rows for now. This is all WIP anyway

 

[darael]

If you have a column for middle names, the below works.

=LOWER(LEFT(A1,1)&LEFT(B1,1)&C1)

John Paul Smith becomes jpsmith

 

[chroniclard]

Should someone without basic Excel knowledge be in charge of this?