Extract numeric data and dates from string

{ 12 Comments }

By Ashish Mathur · · · Leave a comment

In column A, there are some alphanumeric entries.  Somewhere in the alphanumeric entries (beginning, middle or end) there are the following:

1. Dates
2. Numbers (without decimals)
3. Numbers (with decimals)

Furthermore, there may or may not be spaces between numbers/dates and text entries.  In Excel 2010 and prior versions, the only way to solve this problem would be write a formula or a VBA code.  In Excel 2013, one can solve this problem by using the “Flash Fill” feature.  Flash Fill is identifies patterns in your sample output data which you type (for a few entries) and suggests the output for the remaining cells of the range.

You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Hi Asish,

    your solution workbook extract the dates or numbers and that reflects in the output.

    what exactly i am looking is the file names from the below sample (exclude the numbers/dates etc)

    Input Output
    COEP_20121121.txt COEP
    PA0001_20121121.txt PA0001
    PA0105_20121121.txt PA0105
    PA1000.txt PA1000
    IND_COMMENT21-11-2012.txt IND_COMMENT
    part_utilization_60A_22.11.2012.txt part_utilization_60A

    can you please help me on this.

    Regards,
    Kamal M.

    Reply

  • Hi Asish,

    This solution helps but for the last two file names the formula is not working as in the Formula REsults column it shows : “#Value”.

    Can you please help me on this. if this two files names if i get it correctly then it is something the entire problems is resolved.

    Regards,
    Kamal M.

    Reply

  • Hi Asish,

    the moment when myself also download the workbook the Formula Results columns (Column C) looks fine for all the file names.

    On clicking on “Enable Editing” option in the same workbook, it turns to “#Value” for the last two file name.

    Regards,
    Kamal M

    Reply

  • Hi Asish,

    When i unhide the column from E:I then for the last two file names following are the column details:

    40919 #VALUE! #VALUE! #VALUE! #VALUE!
    40950 #VALUE! #VALUE! #VALUE! #VALUE!

    from Column F to I shown error Value.

    Regards,
    Kamal M.

    Reply

    • Hi,

      I opened the file in Excel 2010 and Excel 2013 – in both it opened absolutely fine. Furthermore, sine the dates in the last two files names are 21-11-2012 and 22.11.2012, the numbers in E7:E8 should be 41234 and 41235.

      Recheck.

      Reply

  • Hi Asish,

    for the last two files , the numbers in E7:E8 is coming as 40919 and 40950.

    if i change these values manually as 41234 and 41235 then it is working fine.

    i am also using Excel 2010. not sure where exactly the problem which i am facing while opening your workbook.

    are there any other workaround to resolve this issue ? can you please help me on this.

    Regards,
    kamal m.

    Reply

  • Hi Asish,

    have already tried this option and its not working,

    in E7 the formula is available as : =LOOKUP(1E+100,–RIGHT(SUBSTITUTE(SUBSTITUTE(A7,”.txt”,””),”.”,”/”),ROW(INDIRECT(“$1:”&LEN(SUBSTITUTE(SUBSTITUTE(A7,”.txt”,””),”.”,”/”))))))

    or can you upload your solution workbook once again. probably when i download earlier some issues occured.

    Regards,
    Kamal M.

    Reply

    • It definitely cannot be a download issue else all cells would have got effected. Last evening I could successfully open the workbook in Excel 2010 and Excel 2013.

      I am at and absolute loss.

      Reply