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.
FIND - The starting position of a substring within a larger text string (case sensitive).
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 boolean True or False depending on multiple conditions.
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


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