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: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':Further, this will output John (assuming original text is in A1 cell and previous formula is in B1):
Here:
And you can elaborate it further to get last 2 words :)