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 have an Excel spreadsheet with 1600 rows of data. I need to create usernames from the first and last names of everyone in the list. I can manually go through and get the first letter of their first name and combine it with the last name to create one, but I am wondering if there is a way to automate it in Excel. I tried concatenate but it appears to be all or none as far as combining values goes. I am hoping there is someone that knows how to do this.

Thanks!

share|improve this question

2 Answers 2

up vote 4 down vote accepted

You can nest functions in Excel, so try this, if the first name is in column A and the last name in column B:

=CONCATENATE(LEFT(A1,1),B1)

The "LEFT" function gives you a certain number of characters from the left side of the cell you specify.

share|improve this answer
    
Jenn, thank you so much. You saved me a ton of time. –  fmz Feb 8 '13 at 22:48
4  
the same thing as CONCATENATE is to write the formula =LEFT(A1,1)&B1 –  datatoo Feb 8 '13 at 23:07
    
@datatoo -- cool! I didn't know about the ampersand operator. That'll save me some typing in the future. –  Jenn D. Mar 12 '13 at 21:18

I suggest adding the "TRIM()" function to Jenn's answer to prevent unwanted spaces in your username, also just use the "&" instead of CONCATENATE =left(trim(A1),1) & trim(B1)

share|improve this answer
    
Agree on the trim(); nothing wrong with being tidy. Make sure you do it around just the B1, though, not around the whole function; otherwise you could still end up with a space from in front of the first name. –  Jenn D. Mar 12 '13 at 21:20

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.