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:
Code:
=SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(IF(LEN($A1)-LEN(SUBSTITUTE($A1,"_",""))=4,SUBSTITUTE($A1,"_","|",2),$A1),".","_",4),"_",REPT(" ",100)),COLUMN(A1)*100-99,100)),"|","_")
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.
- Copy the modified formula.
- Select the cell where you want to apply the formula.
- Paste the formula in the formula bar.
- Press Enter.
- Drag the formula to the right to fill the adjacent cells with the formula.