Extracting numbers before and after spaces

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125
Hi,

I have a few hundred cells which all have 4 digits in them seperated by a space.

Eg Cell A1 has 4 16 23 43 in it. Cell A2 has 5 78 76 45 in it.

What I want to do is have formulas in Columns B,C and D which extracts each number out

So cell B1 would have 4 C1 would have 16 and so on.

I have the formula to return the number at the start and at the end of the cell but would anyone be able to help me on what the formula would be for the middle 2?

Also i cant use text to columns here.

thanks in advance
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,708
Office Version
2007
Platform
Windows
Try:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >4 16 23 43</td><td style="text-align:right; ">4</td><td style="text-align:right; ">16</td><td style="text-align:right; ">23</td><td style="text-align:right; ">43</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >5 78 76 45</td><td style="text-align:right; ">5</td><td style="text-align:right; ">78</td><td style="text-align:right; ">76</td><td style="text-align:right; ">45</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=IFERROR(--MID(SUBSTITUTE($A1," ",REPT(" ",99)),((COLUMNS($B$2:B2)-1)*99)+1,99),"")</td></tr></table></td></tr></table>
 

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125
That is excellent, worked a charm thanks

Try:

ABCDE
14 16 23 434162343
25 78 76 455787645

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"></colgroup><tbody>
</tbody>

CellFormula
B1=IFERROR(--MID(SUBSTITUTE($A1," ",REPT(" ",99)),((COLUMNS($B$2:B2)-1)*99)+1,99),"")

<tbody>
</tbody>

<tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,706
Office Version
365
Platform
Windows
I know you asked for a formula solution and Dante has given that to you, but another very quick option (at least once you are used to it) would be
1. Select the column by clicking its heading label
2. Data ribbon tab -> Text to Columns -> Delimited -> Next -> Check 'Space' -> Next -> Destination: B1 -> Finish
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,708
Office Version
2007
Platform
Windows
Glad to help you. Thanks for the feedback.
 

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125
Thanks for the tip but as stated in my initial post I cant use the text to columns here for a few reasons. Appreciate the response all the same

I know you asked for a formula solution and Dante has given that to you, but another very quick option (at least once you are used to it) would be
1. Select the column by clicking its heading label
2. Data ribbon tab -> Text to Columns -> Delimited -> Next -> Check 'Space' -> Next -> Destination: B1 -> Finish
 

Forum statistics

Threads
1,085,807
Messages
5,386,010
Members
401,979
Latest member
cjulianm

Some videos you may like

This Week's Hot Topics

Top