LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
Posts: 1
Default 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

Last edited by goku200 : February 9th 22 at 05:39 PM
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
underscore in email address covered by hyperlink underscore Chuck Bowser Excel Discussion (Misc queries) 1 April 22nd 09 05:47 PM
underscore in functions westronwynde Excel Worksheet Functions 3 June 25th 08 04:04 PM
Splitting 1 cell into 2 based on underscore in text RJF Excel Worksheet Functions 5 May 9th 06 07:50 PM
Concatenate with Underscore in formula CLR Excel Programming 2 August 2nd 05 06:59 PM
Underscore character _ Todd Huttenstine[_2_] Excel Programming 1 November 25th 03 06:08 AM

All times are GMT +1. The time now is 03:39 PM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"