Excel : Split the given string by underscores and select the n-th element

This post shows how to split the given string by the underscores and select the nth element using Excel functions.

Split and Extract

The target string has the following forms

"out_20230805-192844_DLNSS_laCV_30_60_96_180_avg20_cnn12864_node200200_year1986"

This target string means specifications of a model. For example, "DLNSS" means a model name, "laCV_30_60_96_180" means some parameters. "cnn12864" refers to the number of filters in two CNN layers, and so on. This will vary depending on your model specifications.

Excel formula

To split the given string by underscores and select the nth element using useful Excel functions, you can use the following formula. Assuming the string is in cell A1.

=INDEX(FILTERXML(    "<root><element>"&SUBSTITUTE($A$1,"_",    "</element><element>")&"</element></root>",    "//element"), B13)              =LEFT(C13, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},        C13 & "0123456789")) - 1)              =RIGHT(C13, LEN(C13) - LEN(D13))               Colored by Color Scripter

cs

You can use the above formulas in your Excel file as follows. In this case, instead of using a direct number for the nth element, I use a "No" column to select them sequentially. I also show how to extract the character part ("st") and the numeric part ("1986") separately.

For No = 10, three Excel formulas are displayed.

Just copy and paste and adjust the cell positions a bit for your purpose.

\(\blacksquare\)