Find Text Separated by Underscore

bFer

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.

Tomasz Giba

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

bFer

This is works perfectly, thank you!

Tomasz Giba

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

bFer

This is works perfectly, thank you!