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

Cell A1 says 0001 John Smith Cell A2 says 0002 Bill Snyder

I want to basically split this, so one column just shows the numbers (0001, 0002, etc.) and then another column just shows the name.

The first part is easy. Using the function "=LEFT(A1;4)" I can get 0001. How can I grab the name? Using "RIGHT(A1;99)", for example, will grab the entire string "0001 John Smith". Since each name is different in length, I'm not sure what to do. Can I somehow tell it to grab the whole string EXCEPT the first 4 characters? Or somehow tell it to grab the last 2 WORDS instead of a number of characters like it's asking?

share|improve this question

migrated from Jun 2 '11 at 8:53

This question came from our site for professional and enthusiast programmers.

To get sub-string excluding first 4 characters (plus space) you can use MID function:


It will result in 'John Smith Cell A2 says 0002 Bill Snyder'. You can also imitate text splitting with FIND function, for example this formula will output '0001':

LEFT(A1,FIND(" ",A1)-1)

Further, this will output John (assuming original text is in A1 cell and previous formula is in B1):

MID(A1,LEN(B1)+2,FIND(" ",A1,LEN(B1)))


  • A1 - original text
  • LEN(B1)+2 - start position (length of code 0001 + separator + 1)
  • FIND(" ",A1,LEN(B1)) - end position (i.e. next space occurrence)

And you can elaborate it further to get last 2 words :)

share|improve this answer
Thank you so much! – Justin May 28 '11 at 20:50

You must log in to answer this question.