Find Text Separated by Underscore

Options

I am looking for some advice on how to write a formula that will find text between underscores.

Here is an example of what would be in a cell.

ABCD_S1_60Spot_RightOne_Pre_Approval2_16x9_Final_PV_en-US.mp4

The number of characters between each "_" will vary. I'd like the text between every "_" to be in its own cell. Thanks kindly for any advice.

Best Answers

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    Answer ✓
    Options

    (1) - Create help columns - as many as is the maximum possible quantity of underscores (yellow on screenshot below)

    (2) Enter formula into first help column:

    =FIND("_", TEXT@row)

    (3) Enter formula into second helper column and drag it to the right to all others help columns

    =FIND("_", $TEXT@row, [H1]@row + 1)

    (4) Enter formula into first text (result) column:

    =LEFT(TEXT@row, [H1]@row - 1)

    (5) Enter formula into second text column and copy drag it to the right to all other text columns

    =IF([H2]@row = 0, RIGHT($TEXT@row, LEN($TEXT@row) - [H1]@row), MID($TEXT@row, [H1]@row + 1, [H2]@row - [H1]@row - 1))


    Tomasz Giba

  • bFer
    bFer ✭✭
    Answer ✓
    Options

    This is works perfectly, thank you!

Answers

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    Answer ✓
    Options

    (1) - Create help columns - as many as is the maximum possible quantity of underscores (yellow on screenshot below)

    (2) Enter formula into first help column:

    =FIND("_", TEXT@row)

    (3) Enter formula into second helper column and drag it to the right to all others help columns

    =FIND("_", $TEXT@row, [H1]@row + 1)

    (4) Enter formula into first text (result) column:

    =LEFT(TEXT@row, [H1]@row - 1)

    (5) Enter formula into second text column and copy drag it to the right to all other text columns

    =IF([H2]@row = 0, RIGHT($TEXT@row, LEN($TEXT@row) - [H1]@row), MID($TEXT@row, [H1]@row + 1, [H2]@row - [H1]@row - 1))


    Tomasz Giba

  • bFer
    bFer ✭✭
    Answer ✓
    Options

    This is works perfectly, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!