Find Text Separated by Underscore
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_enUS.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

(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

This is works perfectly, thank you!
Answers

(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

This is works perfectly, thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!