# split data from one cell into two with variable number of words

I am working with a large amount of data that comes as an output from a macro enabled spreadsheet.

The data will be in this sort of format: AAA BBB (CCC) (DDD) (EEE)

This means that there are 2-5 "words" all separated by a space, and I need to bring the final "word" into a separate cell whilst striking the final "word" from the rest of the data - so that I am outputting into two different cells from one cell.

• Possible duplicate of extract text from the last special character in excel Nov 11, 2019 at 12:06
• @Akina i'd say its related not a duplicate, as the question you linked only required the LAST separator, whereas I need all text on both sides of the separating character, just in different cells - an expansion of the question Nov 11, 2019 at 12:07
• ??? You obtain the part after the last separator. You may calculate its length. You may calculate source length. What a problem to get the rest of chars from the beginning? it is a duplicate, the only problem is to apply it correctly. Nov 11, 2019 at 12:18
• Imagine you have some value, which you need to divide, in A1. Imagine you apply some formula in C1, and it gives you the last group after the last space. Now put the formula `=LEFT(A1, LENGTH(A1) - LENGTH(C1) -1)` into B1 - and you'll obtain the full result which you need. Nov 11, 2019 at 12:30
• @Akina sounds like you should post that as an answer.
– Mokubai
Nov 11, 2019 at 12:55

Not an expert, but I recently had to do something similar.

To get the last word try this:

`````` =TRIM(RIGHT(SUBSTITUTE(cellwithtexthere," ",REPT(" ",100)),100))
``````

To get all words except the last one use the function from the other side (from left to right)

Mostly making @Akina's aic into an actual answer here but:

Using my original example:

AAA BBB (CCC) (DDD) (EEE) - to get everything apart from the final word:

Where the original word is in cell A1, and you have already outputted the final word using @Another Noone's answer into cell B1, you can use this formula:

=LEFT(A1, (LEN(A1) - (LEN(B1) + 1)))

This formula works because it counts the length of the final word, adds one, then makes the LEFT function return everything apart from the final word plus one character for the space.