Split Text

Options


Can someone assist me?

I need a formula on how to split the text for this below.

Ex.

LOW - VS-87 - Service-FS Battery Replacement Lead-MEGA MALL-1259410

I need a to have a column that would write MEGA MALL only.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Marilen.Navarro103391

    Please use the formula I provided. It was intentional for the character "~" to be included.

    =MID(Name@row, FIND("~", SUBSTITUTE(Name@row, "-", "~", 5)) + 1, FIND("~", SUBSTITUTE(Name@row, "-", "~", 6)) - 1 - FIND("~", SUBSTITUTE(Name@row, "-", "~", 5)))

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Marilen.Navarro103391

    Assuming the format of your entire text column is formatted as above, your formula is:

    =MID([Your Text Column]@row, FIND("~", SUBSTITUTE([Your Text Column]@row, "-", "~", 5)) + 1, FIND("~", SUBSTITUTE([Your Text Column]@row, "-", "~", 6)) - 1 - FIND("~", SUBSTITUTE([Your Text Column]@row, "-", "~", 5)))

    Don't forget you will have to change the name of the [Your Text Column] to your actual column name

    Will this work for you?

    Kelly

  • Marilen.Navarro103391
    Options

    I have #INVALID VALUE

    =MID(Name@row, FIND("-", SUBSTITUTE(Name@row, "-", "-", 5)) + 1, FIND("-", SUBSTITUTE(Name@row, "-", "-", 6)) - 1 - FIND("-", SUBSTITUTE(Name@row, "-", "-", 5)))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Marilen.Navarro103391

    Please use the formula I provided. It was intentional for the character "~" to be included.

    =MID(Name@row, FIND("~", SUBSTITUTE(Name@row, "-", "~", 5)) + 1, FIND("~", SUBSTITUTE(Name@row, "-", "~", 6)) - 1 - FIND("~", SUBSTITUTE(Name@row, "-", "~", 5)))

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!