How do I return text from text string with the space as a delimiter?
Hello,
I am trying to extract each word, or a set of words from a text string that has spaces in between.
Example:
AW (Ground) AIRTOP Tropical Day Cart 500mL
How do I just return each word by itself in different columns?
Thank you!
Answers

For the column pulling the first pc of information you would use the below:
=IFERROR(LEFT([TEXT_COLUMN]@row + " ", FIND(" ", [TEXT_COLUMN]@row + " ")  1), "")
For the other columns use the below, and increment the number bolded.
=MID([TEXT_COLUMN]@row + " ", FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " ", " ", "~", 1)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " ", " ", "~", 2))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " ", " ", "~", 1))  1)
=MID([TEXT_COLUMN]@row + " ", FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " ", " ", "~", 2)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " ", " ", "~", 3))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " ", " ", "~", 2))  1)

You are absolutely amazing! Thank you!
Is there a formula that skips a space?
Meaning if I had:
AW (Ground) AIRTOP Tropical Day Cart 500mL
Can I extract Tropical and Day together?

Yes, increase the second bolded number by 1. So for your example the middle number is 5 and the others are 2
=MID([TEXT_COLUMN]@row + " ", FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " ", " ", "~", 3)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " ", " ", "~", 5))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " ", " ", "~", 3))  1)
Help Article Resources
Categories
Check out the Formula Handbook template!