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.

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 stackoverflow.com Jun 2 '11 at 8:53

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

add comment

1 Answer 1

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

MID(A1,6,LEN(A1))

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

Here:

  • 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.