Reading data and extracting only the middle information

[sneakyla]

Hello!

I'm currently trying to find a formula to extract data and return only the middle of another cell.

For example.

Cell A1
300 1 2 3 3 2 1 3 4 40

I want a formula that can extract only "1 2 3 3 2 1 3 4" and also include a comma in between.

So far, my solution to this is using the MID formula. I'm currently using =MID(A1,5,1)&","&MID(A1,7,1) and so on. This formula works perfectly and gives me the exact result that I'm looking for which is 1,2,3,3,2,1,3,4 with the commas included.

However, the problem occurs when there are DOUBLE DIGIT numbers. If the cell was 300 12 13 14 15 18 21 400 the MID formula would read the 5th, 7th, 9th line only and give me the wrong data.

Would anyone know how to fix this to display both single and double digit numbers?

Thank you!

 

[PeteWright]

If you only have character groups (letters or digits) each one separated by spaces, this should do it:

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	300 1 2 3 3 2 1 3 4 40	4	3001233213440	22	13	9	300 1 2 3 3 2 1 3 4~40	20	2	15	1 2 3 3 2 1 3 4	1,2,3,3,2,1,3,4	1,2,3,3,2,1,3,4
Sheet9

Cell Formulas
Range	Formula
B2	B2	= FIND(" ",A2)
C2	C2	= SUBSTITUTE(A2," ","")
D2	D2	= LEN(A2)
E2	E2	= LEN(C2)
F2	F2	= D2 - E2
G2	G2	= SUBSTITUTE(A2," ","~",F2)
H2	H2	= FIND("~",G2)
I2	I2	= D2 - H2
J2	J2	= D2 - B2 - I2 - 1
K2	K2	= MID(A2,B2+1,J2)
L2	L2	= SUBSTITUTE(K2," ",",")
M2	M2	= SUBSTITUTE(MID(A2,FIND(" ",A2)+1,LEN(A2) - FIND(" ",A2) - (LEN(A2) - FIND("~",SUBSTITUTE(A2," ","~",LEN(A2) - LEN(SUBSTITUTE(A2," ",""))))) - 1)," ",",")

The original text is in cell B2 and the formula / result in cell M2

All other cells are 'helper cells' to show you the calculation steps.
Feel free to ask.

 

[Peter_SSs]

See if this would work for you.

22 11 15.xlsm
	A	B
1	300 1 2 3 3 2 1 3 4 40	1,2,3,3,2,1,3,4
2	1 23 156 1 25 26 23659 33	23,156,1,25,26,23659
Mid section

Cell Formulas
Range	Formula
B1:B2	B1	=SUBSTITUTE(TRIM(REPLACE(LEFT(SUBSTITUTE(A1," ",REPT(" ",20)),LEN(SUBSTITUTE(A1," ",REPT(" ",20)))-20),1,20,""))," ",",")

 

[sneakyla]

Thank you all for the amazing formula! Both of those works successfully. I really appreciate it. Thank you again!!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.