+ Reply to Thread
Results 1 to 12 of 12

formula to extract numbers from within a cell

  1. #1
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE 2016
    Posts
    26

    formula to extract numbers from within a cell

    We have data that comes into excel with 2 or 3 number sets in a cell. For example 10-9 or 8-12-3 would be in the cell as text. Need formulas to extract each set of numbers separated by the dashes into its own column. Sometimes there is 1 number in the number set, sometimes 2. Sometimes there is not a 3rd set of numbers in the cell and the last formula would need to return a 0 if the cell only has 1 dash (meaning only 2 sets of numbers).

    The first formula for the 1st set of numbers i got
    On the second formula I can't figure out how to get just the numbers to the right of the 1st dash and nothing else
    On the 3rd formula I am completely lost
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,460

    Re: formula to extract numbers from within a cell

    Try this in L3:

    =TRIM(MID(SUBSTITUTE("-"&$J3,"-",REPT(" ",100)),COLUMNS($L3:L3)*100,100))

  3. #3
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE 2016
    Posts
    26

    Re: formula to extract numbers from within a cell

    Thank you but it seems that the formula I had in L3 was working fine. My problem is I need a working formula for M3 and N3

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,460

    Re: formula to extract numbers from within a cell

    Quote Originally Posted by RICK JAMES View Post
    the formula I had in L3 was working fine.
    If there is only a number in J3, the formula will return an error.

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,460

    Re: formula to extract numbers from within a cell

    Also, you can use "Text to Columns" with the separator: "-"

  6. #6
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    671

    Re: formula to extract numbers from within a cell

    L3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    M3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    N3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Select L3:N3 and fill down as far as needed.

    Could J3 ever contain just 1 section and no hyphens?
    Last edited by hrlngrv; 10-17-2020 at 10:44 PM.

  7. #7
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE 2016
    Posts
    26

    Re: formula to extract numbers from within a cell

    Formulas for L3 and M3 do the trick. The formula for N3 gives everything as a tenth of the number ie .3 instead of 3. Not sure how to fix that.

  8. #8
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    671

    Re: formula to extract numbers from within a cell

    Sorry.

    N3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    01-05-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    289

    Re: formula to extract numbers from within a cell

    Formula for L3 = VALUE(LEFT(SUBSTITUTE(J3,"-",REPT(" ",100)),100))
    For M3 = VALUE((RIGHT(LEFT(SUBSTITUTE(J3,"-",REPT(" ",100)),200),100)))
    For N3 = IF(LEN(J3)=LEN(F3)+LEN(G3)+1,0,VALUE((RIGHT(SUBSTITUTE(J3,"-",REPT(" ",100)),100))))

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,496

    Re: formula to extract numbers from within a cell

    An alternative solution based upon Power Query/Get & Transform found on the Data Tab of the Ribbon

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    Column1.1 Column1.2 Column1.3
    2
    21
    3
    1
    3
    12
    18
    2
    4
    5
    16
    0
    5
    20
    3
    1
    6
    22
    6
    0
    7
    9
    17
    0
    Sheet: Sheet2

    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE 2016
    Posts
    26

    Re: formula to extract numbers from within a cell

    No apology necessary. Thank you!

  12. #12
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE 2016
    Posts
    26

    Re: formula to extract numbers from within a cell

    Thank you all. I guess with excel it just comes down to more than one way to skin the xlsx. Not sure why one would be better than another but I will take the working formulas.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Single cell formula to extract whole numbers?
    By B.W.B. in forum Excel General
    Replies: 2
    Last Post: 04-03-2019, 09:38 AM
  2. Replies: 6
    Last Post: 12-14-2018, 12:11 PM
  3. [SOLVED] Formula to extract numbers from cell?
    By ExcelBG in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-18-2017, 09:08 AM
  4. Is there a formula that will extract the numbers to the left of the cell
    By benji1973 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-04-2015, 10:11 AM
  5. Extract numbers from a sum formula inside a cell
    By cycloneclau in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2014, 01:55 PM
  6. [SOLVED] Formula to Extract Numbers from Text Cell
    By MHamid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2012, 04:08 PM
  7. [SOLVED] extract number and use in formula from text & numbers in cell
    By ivory_kitten in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-14-2006, 12:45 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1