Return the second word

You can return the second word from a text string by using this formula
If there is only one word then this formula returns an empty string.
link - https://www.atlaspm.com/toms-tutorials-for-excel/


 A
1First Second Third Fourth
2=IF(OR(LEN(A1)=0,COUNT(FIND(" ",A1))=0),"",TRIM(REPLACE(LEFT(A1,FIND(" ",A1&" ",FIND(" ",A1,1)+1)),1,FIND(" ",A1),""))) = Second
3 
4One 2 Three
5=IF(OR(LEN(A4)=0,COUNT(FIND(" ",A4))=0),"",TRIM(REPLACE(LEFT(A4,FIND(" ",A4&" ",FIND(" ",A4,1)+1)),1,FIND(" ",A4),""))) = 2

Built-in Functions

COUNT - The number of numerical values in a list, table or cell range.
FIND - The starting position of a substring within a larger text string.
IF - The value based on whether a condition is True or False.
LEFT - The first or left most characters in a text string.
LEN - The number of characters in a text string.
OR - The logical OR for any number of arguments.
REPLACE - The text string after replacing characters in a specific location.
TRIM - The text string with all extra spaces removed from the beginning, middle and end.


Related Formulas

Return the acronym from a text string
Return the nth word


© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited Top