Copying partial cell to another cell in OpenOffice Calc

openoffice

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?

Best Answer

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

Related Question