Formula help please

Hi Joshua,

You formula shall work assuming 1) you have the text in column B, not numbers; 2) text from B appears only at very right of A and not repeated in the middle; and if you deduct 1 from the search result since it gives you the position where textB starts in A. Other words

=LEFT(A2, SEARCH(B2,A2,1)-1)

Another approach could be not to use Column B at all if only assume what trailing word is always separated from the rest of the string by space.

First, you calculate how many spaces are in your string compare length of it with with length of the same string with removed spaces

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

Second, you substitute the latest space in initial string by any character which definitely could be not within the text, in this case with unprintable character CHAR(160):

=SUBSTITUTE(A2," ",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2," ","")))

next find the position of this character within the string

=FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

and take left part of the text before that position

=LEFT(A2,
FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

To avoid an error if your text doesn't have spaces at all better to check and return initial text for such case

=IF(ISERROR(FIND(" ",A2)),A2,LEFT(A2,
FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

And finally, your text in A could have trailing spaces, we need to trim them for the correct calculations, other words use TRIM(A2) instead of A2. Thus final formula will be

=IF(ISERROR(FIND(" ",TRIM(A2))),A2,LEFT(TRIM(A2),
FIND(CHAR(160),SUBSTITUTE(TRIM(A2)," ",CHAR(160),LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))))