Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

You must log in to answer this question.

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