Results 1 to 7 of 7

Thread: Extracting numbers before and after spaces

  1. #1
    Board Regular
    Join Date
    Jul 2016
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    thanks in advance

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,337
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Extracting numbers before and after spaces

    Try:

     ABCDE
    14 16 23 434162343
    25 78 76 455787645

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

  3. #3
    Board Regular
    Join Date
    Jul 2016
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting numbers before and after spaces

    That is excellent, worked a charm thanks

    Quote Originally Posted by DanteAmor View Post
    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. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,005
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default 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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,337
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Extracting numbers before and after spaces

    Glad to help you. Thanks for the feedback.
    Regards Dante Amor

  6. #6
    Board Regular
    Join Date
    Jul 2016
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Quote Originally Posted by Peter_SSs View Post
    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. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,005
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extracting numbers before and after spaces

    Quote Originally Posted by stroffso View Post
    .. as stated in my initial post I cant use the text to columns here for a few reasons. .
    Ah, sorry, I missed that.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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