Splitting underscore formula
 

I have 5 excel records in the following format:
12345_v1.0_TEST Name [12345] v1.5_01.01.2022.html
12345_v1.0_TEST_Name [12345]_01.01.2022.html
12345_v1.0_TESTName [12345]_01.01.2022.xlsx
12345_v1.0_TEST Name [12345]_01.01.2022.png
12345_v1.0_TEST Name [12345]_01.01.2022.xlsx

When I use the following formula:
=SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(IF(LEN( $A1)-LEN(SUBSTITUTE($A1,"_",""))=4,SUBSTITUTE($A1,"_"," |",3),$A1),".","_",4),"_",REPT(" ",100)),COLUMN(A1)*100-99,100)),"|","_")

It should split the information into separate columns like this:

12345_v1.0_TEST Name [12345] v1.5_01.01.2022.html 12345 v1.0 TEST Name [12345] v1.5 01.01 2022.html (Returns wrong ouput)

12345_v1.0_TEST_Name [12345]_01.01.2022.html 12345 v1.0 TEST_Name [12345] 01.01.2022 html

12345_v1.0_TESTName [12345]_01.01.2022.xlsx 12345 v1.0 TESTName [12345] 01.01.2022 xlsx

12345_v1.0_TEST Name [12345]_01.01.2022.png 12345 v1.0 TEST Name [12345] 01.01.2022 png

12345_v1.0_TEST Name [12345]_01.01.2022.xlsx 12345 v1.0 TEST Name [12345] 01.01.2022 xlsx

It works fine on the last 4 records except for the first one. What exactly am I missing in the formula? Not sure how to tweak this to get my desired results. Any help is greatly appreciated :)

Answer: Splitting underscore formula
 

The issue with the formula is that it is splitting the fourth underscore instead of the third underscore in the first record. To fix this, you can modify the formula to split at the third underscore instead of the fourth.

Here's the modified formula:

The only change made is in the IF statement where we replaced the number 3 with 2 to split at the third underscore instead of the fourth. This should give you the desired output for all 5 records.

1. Copy the modified formula.
2. Select the cell where you want to apply the formula.
3. Paste the formula in the formula bar.
4. Press Enter.
5. Drag the formula to the right to fill the adjacent cells with the formula.