Reading data and extracting only the middle information

sneakyla

New Member
Joined
Aug 11, 2021
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 20, 2020
Messages
144
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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.
Feel free to ask.
 
Upvote 0
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,487
Office Version
  1. 365
Platform
  1. Windows
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,""))," ",",")
 
Upvote 0

sneakyla

New Member
Joined
Aug 11, 2021
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Thank you all for the amazing formula! Both of those works successfully. I really appreciate it. Thank you again!!
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,487
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top