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!
@Desiree500
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)
@Leibel Shuchat
You are absolutely amazing! Thank you!
Is there a formula that skips a space?
Meaning if I had:
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)
ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.
