How do I return text from text string with the space as a delimiter?

Desiree500

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!

Leibel S

@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)

Desiree500

@Leibel Shuchat

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?

Leibel S

@Desiree500

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)