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