Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 ![]() Last edited by goku200 : February 9th 22 at 05:39 PM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
underscore in email address covered by hyperlink underscore | Excel Discussion (Misc queries) | |||
underscore in functions | Excel Worksheet Functions | |||
Splitting 1 cell into 2 based on underscore in text | Excel Worksheet Functions | |||
Concatenate with Underscore in formula | Excel Programming | |||
Underscore character _ | Excel Programming |