+ Reply to Thread
Results 1 to 6 of 6

Extracting only numeric data from combined numeric & non-numeric data

  1. 11-20-2013, 01:47 AM #1
    paradise2sr is offline
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2024
    Posts
    1,384

    Extracting only numeric data from combined numeric & non-numeric data

    Hi,

    I have a problem in separating the numeric data.For example

    Row Column A
    1 Manakamana Flour Mills Pvt.Ltd.Siliguri
    2 Kelimo Pvt.Ltd.ltm(300064698)

    There has been a lot of data like this but of varying length.Hence I want a formula that separates numeric data from both cases just as mentioned in row 1 where there is no numeric data and in row 2 where there is numeric as well as non numeric.

    Secondly,I want to separate further list of names (Non numeric Data) from above row 1 and row 2.

    Can anyone suggest me the right formula for this.

    With Best Rgds,
    Suresh
    Reply With QuoteRegister To Reply
  2. 11-20-2013, 02:05 AM #2
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Extracting only numeric data from combined numeric & non-numeric data

    This will extract the numeric from the cell,,,
    =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre

    You can then build that into a =substitute() function to give you just the text
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 11-20-2013, 02:34 AM #3
    paradise2sr is offline
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2024
    Posts
    1,384

    Re: Extracting only numeric data from combined numeric & non-numeric data

    Dear Sir,

    Thanks for the reply.I got the first result.But could not get the reply to my next question is that I want the remaining or non numeric data too.How could I extract those ?How to use substitute function in this case as you have said for extracting Non-numeric data?The result required in the next column would be as follows-

    Manakamana Flour Mills Pvt.Ltd.Siliguri
    Kelimo Pvt.Ltd.ltm



    With Best Rgds,
    suresh
    Last edited by paradise2sr; 11-20-2013 at 02:48 AM.
    Reply With QuoteRegister To Reply
  4. 11-20-2013, 02:55 AM #4
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Extracting only numeric data from combined numeric & non-numeric data

    If you dont mind keeping the braces (), then use this...
    =SUBSTITUTE(A1,LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))),"")

    If you want to get rid of the braces (), then use this instead...
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))),""),"(",""),")","")
    Reply With QuoteRegister To Reply
  5. 11-20-2013, 03:08 AM #5
    paradise2sr is offline
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    MS Excel 2024
    Posts
    1,384

    Re: Extracting only numeric data from combined numeric & non-numeric data

    Thanks the 2nd part works better.However,I was trying with simple substitute function but it was giving me braces().

    =SUBSTITUTE(A1,B1,"",1)

    Thanks for the help.

    With Best Rgds,
    Suresh
    Reply With QuoteRegister To Reply
  6. 11-20-2013, 11:51 AM #6
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Extracting only numeric data from combined numeric & non-numeric data

    Happy to help and thanks for the feedback
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extracting Numeric data from alphanumeric
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-06-2013, 03:23 AM
  2. [SOLVED] From alfa numeric data, substitute numeric data.
    By manharji in forum Excel General
    Replies: 6
    Last Post: 04-06-2012, 10:49 PM
  3. Extracting Numeric Values from an Alpha/Numeric String
    By Delkath in forum Excel General
    Replies: 5
    Last Post: 10-27-2010, 02:36 PM
  4. Extracting specific numeric data from cell
    By magermaennchen in forum Excel General
    Replies: 2
    Last Post: 05-26-2010, 09:36 AM
  5. Extracting Numeric data from a string
    By roasty_1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-04-2008, 06:51 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1