# Reading data and extracting only the middle information

#### sneakyla

##### New Member
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!

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### PeteWright

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

Cell Formulas
RangeFormula
B2B2= FIND(" ",A2)
C2C2= SUBSTITUTE(A2," ","")
D2D2= LEN(A2)
E2E2= LEN(C2)
F2F2= D2 - E2
G2G2= SUBSTITUTE(A2," ","~",F2)
H2H2= FIND("~",G2)
I2I2= D2 - H2
J2J2= D2 - B2 - I2 - 1
K2K2= MID(A2,B2+1,J2)
L2L2= SUBSTITUTE(K2," ",",")
M2M2= 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.

#### Peter_SSs

##### MrExcel MVP, Moderator
See if this would work for you.

22 11 15.xlsm
AB
1300 1 2 3 3 2 1 3 4 401,2,3,3,2,1,3,4
21 23 156 1 25 26 23659 3323,156,1,25,26,23659
Mid section
Cell Formulas
RangeFormula
B1:B2B1=SUBSTITUTE(TRIM(REPLACE(LEFT(SUBSTITUTE(A1," ",REPT(" ",20)),LEN(SUBSTITUTE(A1," ",REPT(" ",20)))-20),1,20,""))," ",",")

#### sneakyla

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

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Glad we could help. Thanks for the follow-up.

Replies
11
Views
302
Replies
3
Views
57
Replies
16
Views
289
Replies
5
Views
234
Replies
0
Views
70

1,186,381
Messages
5,957,530
Members
438,310
Latest member
excelvolution

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back