Split cell based on a character

[DavidAC]

I have a list of 1000 rows similar to "Case_01_Allocate Further Staff_V4.xlsx" or "Casenote_01_Casenote_Filter_V5.xlsx" I need to be able to split this field into 2 separate columns based on the second _ so the fields should end up looking like below.
Any help greatly appreciated

Case_01 _Allocate Further Staff_V4.xlsx
Casenote _01_Casenote_Filter_V5.xlsx

 

[RobP]

Which Excel version do you use ?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[RobP]

Maybe this can help:

The TEXTPLIT works only if you have O365. Else you can use the other formulas individually in each cell for first / last parts.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Case_01_Allocate Further Staff_V4.xlsx				Case_01	Allocate Further Staff_V4.xlsx				Case_01	Allocate Further Staff_V4.xlsx
2	Casenote_01_Casenote_Filter_V5.xlsx				Casenote_01	Casenote_Filter_V5.xlsx				Casenote_01	Casenote_Filter_V5.xlsx
3
Sheet1

Cell Formulas
Range	Formula
J1:J2	J1	=LEFT(A1,FIND("_",A1,FIND("_",A1,1)+1)-1)
K1:K2	K1	=RIGHT(A1,LEN(A1)-FIND("_",A1,FIND("_",A1,1)+1))
E1:F2	E1	=TEXTSPLIT(LEFT(A1,FIND("_",A1,FIND("_",A1,1)+1)-1)&"*"&RIGHT(A1,LEN(A1)-FIND("_",A1,FIND("_",A1,1)+1)),"*")
Dynamic array formulas.

No doubt @Fluff will be along soon with something better as usual..

 

[Fluff]

Not better, just different.

Fluff.xlsm
	A	B	C
1
2	Case_01_Allocate Further Staff_V4.xlsx	Case_01	Allocate Further Staff_V4.xlsx
3	Casenote_01_Casenote_Filter_V5.xlsx	Casenote_01	Casenote_Filter_V5.xlsx
4
5	Case_01_Allocate Further Staff_V4.xlsx	Case_01	Allocate Further Staff_V4.xlsx
6	Casenote_01_Casenote_Filter_V5.xlsx	Casenote_01	Casenote_Filter_V5.xlsx
7
8	Case_01_Allocate Further Staff_V4.xlsx	Case_01	Allocate Further Staff_V4.xlsx
9	Casenote_01_Casenote_Filter_V5.xlsx	Casenote_01	Casenote_Filter_V5.xlsx
10
Main

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTBEFORE(A2,"_",2)
C2:C3	C2	=TEXTAFTER(A2,"_",2)
B5:C6	B5	=TEXTSPLIT(SUBSTITUTE(A5,"_","^",2),"^")
B8:B9	B8	=LEFT(A8,FIND("^",SUBSTITUTE(A2,"_","^",2))-1)
C8:C9	C8	=SUBSTITUTE(A8,B8&"_","")
Dynamic array formulas.