+ 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 PRO 2021
    Posts
    56

    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
    2016
    Posts
    5,891

    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 PRO 2021
    Posts
    56

    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
    2016
    Posts
    5,891

    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
    2016
    Posts
    5,891

    Re: formula to extract numbers from within a cell

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

  6. #6
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    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 PRO 2021
    Posts
    56

    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
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    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 Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  11. #11
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE PRO 2021
    Posts
    56

    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 PRO 2021
    Posts
    56

    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