Explanation
FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars . The LEFT function then extracts characters starting at the left side of the text, up to (position - 1).
Handling one word
If a cell contains only one word, this formula returns an error. One way to fix this problem is to wrap the original formula in the IFERROR function like so:
=IFERROR(LEFT(B4,FIND(" ",B4)-1),B4)
Translated: if an error occurs, return the original text.
Another simple way to handle the error is to append a space to the cell value before running FIND:
=LEFT(B4,FIND(" ",B4&" ")-1)
This ensures that FIND will always find at least one space, and will therefore not throw an error. When a cell contains more than one word, there is no impact because the formula works only with the first space.
Explanation
This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and “flood” the text with spaces where we find one. Then we come back and grab text from the right.
Working from the inside out, we use the SUBSTITUTE function to find all line breaks (char 10) in the text, and replace each one with 200 spaces:
SUBSTITUTE(B5,CHAR(10),REPT(" ",200))
After the substitution, the looks like this (with hyphens marking spaces for readability):
line one----------line two----------line three
With 200 spaces between each line of text.
Next, the RIGHT function extracts 200 characters, starting from the right. The result will look like this:
-------line three
Finally, the TRIM function removes all leading spaces, and returns the last line.
Note: 200 is an arbitrary number that represents the longest line you expect to find in a cell. If you have longer lines, increase this number as needed.
Mac version
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(13),REPT(" ",200)),200))
In Excel 365 , both Win and Mac versions of Excel use CHAR(10) as a line break.
