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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!