1

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.

If anything is unclear please ask, thanks in advance.

6
  • Possible duplicate of extract text from the last special character in excel
    – Akina
    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.
    – Akina
    Nov 11, 2019 at 12:18
  • 1
    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.
    – Akina
    Nov 11, 2019 at 12:30
  • 2
    @Akina sounds like you should post that as an answer.
    – Mokubai
    Nov 11, 2019 at 12:55

2 Answers 2

1

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)

0
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.