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

Options

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!

Tags:

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

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

    @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
    Leibel S ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!