Mid find search

[termsig]

Hello,

I'm trying split the cell into multiple data sets however i have some difficulties to get some of them pulling correctly.

[TABLE="width: 483"]
<tbody>[TR]
[TD="width: 483"]Data sets is looking like below and it's divided from each information by "|" and each data set might have different number of characters. INFO1|INFO2|INFO3|INFO4|INFO5 [/TD]
[/TR]
</tbody>[/TABLE]

I did manage to get the 1,2,3 split out from string however i have difficulties for 4 and 5.

INFO2
=MID(A1, SEARCH("|",A5) + 1, SEARCH("|",A5,SEARCH("|",A5)+1) - SEARCH("|",A5) - 1)
INFO3
=MID(A5, FIND("|",A5, SEARCH("|",A5)+1)+1, SEARCH("|",A5,SEARCH("|",A5)-1) )

Anyone can help how i can get 4 and 5 sorted as for some reason nothing is working correctly.

Thanks T.

 

[Fluff]

You can do that with Text to Columns.
On page 2 select Other & enter | in the box

 

[termsig]

You can do that with Text to Columns.
On page 2 select Other & enter | in the box

i know that it's possible and it's probably the easiest way to do it however i'm trying remove the additional process where people who will use spreadsheet would need to this every time therefore the way with the function instead.

 

[wideboydixon]

Generic solution is here:

https://exceljet.net/formula/split-text-with-delimiter

Example:

Book1
	A	B	C	D	E	F
1		First	Second	Third	Fourth	Fifth
2	INFO1|INFO2|INFO3|INFO4|INFO5	INFO1	INFO2	INFO3	INFO4	INFO5
3	WIDE|BOY|DIXON|RULESMYWORLD	WIDE	BOY	DIXON	RULESMYWORLD
4	1|2|3333333|44|5	1	2	3333333	44	5
Sheet1

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE($A2,"|",REPT(" ",LEN($A2))),1,LEN($A2)))
C2	=TRIM(MID(SUBSTITUTE($A2,"|",REPT(" ",LEN($A2))),LEN($A2)+1,LEN($A2)))
D2	=TRIM(MID(SUBSTITUTE($A2,"|",REPT(" ",LEN($A2))),2*LEN($A2)+1,LEN($A2)))
E2	=TRIM(MID(SUBSTITUTE($A2,"|",REPT(" ",LEN($A2))),3*LEN($A2)+1,LEN($A2)))
F2	=TRIM(MID(SUBSTITUTE($A2,"|",REPT(" ",LEN($A2))),4*LEN($A2)+1,LEN($A2)))

WBD

 

[jtakw]

Hi,

Book1
	A	B	C	D	E	F	G
1	INFO1|INFO2|INFO3|INFO4|INFO5	INFO1	INFO2	INFO3	INFO4	INFO5
2	INFO1|someOTHERinfo|YetMoreINFO3|evenMore 4|Hopefully last one-INFO5|Nope, this is Last INFO	INFO1	someOTHERinfo	YetMoreINFO3	evenMore 4	Hopefully last one-INFO5	Nope, this is Last INFO
Sheet177

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE($A1,"|",REPT(" ",300)),COLUMNS($B1:B1)*300-299,300))

B1 formula copied down and across as far as needed.