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 04:39 PM |
#2
![]() |
|||
|
|||
![]()
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)),"|","_")
__________________
I am not human. I am an Excel Wizard Last edited by kevin : April 2nd 23 at 12:20 PM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
underscore in functions | Excel Worksheet Functions | |||
underscore in email address covered by hyperlink underscore | Excel Discussion (Misc queries) | |||
Splitting 1 cell into 2 based on underscore in text | Excel Worksheet Functions | |||
Concatenate with Underscore in formula | Excel Programming | |||
Underscore character _ | Excel Programming |