# Thread: Extracting numbers before and after spaces Thanks:  1 Post #5345425 (1) Likes:  1 Post #5345425 (1)

1. ## Extracting numbers before and after spaces

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.

2. ## Re: Extracting numbers before and after spaces

Try:

 A B C D E 1 4 16 23 43 4 16 23 43 2 5 78 76 45 5 78 76 45

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

3. ## Re: Extracting numbers before and after spaces

That is excellent, worked a charm thanks

Originally Posted by DanteAmor
Try:

 A B C D E 1 4 16 23 43 4 16 23 43 2 5 78 76 45 5 78 76 45

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

4. ## Re: Extracting numbers before and after spaces

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

6. ## Re: Extracting numbers before and after spaces

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

Originally Posted by Peter_SSs
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

7. ## Re: Extracting numbers before and after spaces

Originally Posted by stroffso
.. as stated in my initial post I cant use the text to columns here for a few reasons. .
Ah, sorry, I missed that.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•