Find last instance of a character in string and keep anything that comes before

Question

I have the following data and I need to get rid of the numbers. This means I will have to find that last occurence of the character " " and keep everything that comes before however I dont know how to write the formula.

The length of the text varies and it can also be multiple words. The numbers are not longer than 3 digits.

**String**
abc 61
abc 79
ac 1
ab 123
a bc 12

Thanks!

Answer 1

Try this formula:

=SUBSTITUTE(A1, TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 99)), 99)), "")

Result:

_{(The formula in the screenshot uses semicolons due to my regional settings.)}

---

Breaking it down:

1. =SUBSTITUTE(A1, " ", REPT(" ", 99))

   This replaces each occurrence of the desired delimiter (in your case, space) with 99 spaces. The exact number isn't important, so long as it's larger than the length of the longest individual word.

2. =TRIM(RIGHT(..., 99))

   This takes the rightmost 99 characters (it's important to use the same number here as in step 1) and trims them (removes whitespace from the beginning and end). In essence, this gives you the text after the last whitespace.

3. =SUBSTITUTE(A1, ..., "")

   Since you wanted the text before the last whitespace, this simply takes the result of step 2 and replaces it with an empty string, thus removing it. You are now left with the contents of the cell up to and including the last whitespace character. If you want to remove the trailing space as well, you can run it through =TRIM() one more time.

Answer 2

I'd use this formula -

=LEFT(A1,LOOKUP(9.9999999999E+307,FIND(" ",A1,ROW($1:$1024))))

It finds the last instance of and uses that to define how many characters to print starting at the left

Edit - added $1:$1024 because for some reason ROW() was failing on shorter strings.

Answer 3

KEEP BEFORE LAST function:

=TRIM(LEFT(SUBSTITUTE(A1;"TARGETCHAIN";REPT(" ";LEN(A1)));LEN(A1)))

French version: ;-)

=SUPPRESPACE(GAUCHE(SUBSTITUE(A1;"TARGETCHAIN";REPT(" ";NBCAR(A1)));NBCAR(A1)))

.. works perfectly when "TARGETCHAIN" is set to whitespace: " "