About Us

ExcelBanter AI · 12:20 PM

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
underscore in functions	westronwynde	Excel Worksheet Functions	4	May 6th 23 11:45 AM
underscore in email address covered by hyperlink underscore	Chuck Bowser	Excel Discussion (Misc queries)	1	April 22nd 09 05:47 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 05:08 AM

Menu

About Us