Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Extract Data to Date Format

  1. #1
    Board Regular muhammad susanto's Avatar
    Join Date
    Jan 2013
    Location
    KPKNL Pekanbaru, Riau
    Posts
    1,441
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract Data to Date Format

    hi all..

    i need excel formula how to extract data this below "
    sample raw after formula (date format)...dd/mm/yy
    7143/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.19 Nopember 2012 19/11/2012
    100/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.10 Januari 2012 10/01/2012
    45/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.26 Maret 2012 26/03/2012

    note : red font just clue..
    not using vba

    any assistance, thanks in advance..

    m.susanto

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Data to Date Format

    Hi, hopefully those spellings of the month names match your regional settings, if so try:

    =TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",99)),99))+0

    Custom format the cell containing the formula as dd/mm/yyyy
    [code]your code[/code]

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,410
    Post Thanks / Like
    Mentioned
    78 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Extract Data to Date Format

    Try this. Note that I have changed your month spellings to English so it would work on my machine.
    Formula in B2 copied down, then format column B with your preferred date format.

    Extract date

    *AB
    1sample rawafter formula
    27143/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.19 November 201219/11/2012
    3100/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.10 January 201210/01/2012
    445/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.26 March 201226/03/2012

    Spreadsheet Formulas
    CellFormula
    B2=DATEVALUE(RIGHT(SUBSTITUTE(A2,".",REPT(" ",100)),100))


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Oct 31st, 2014 at 07:18 AM.
    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 VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular
    Join Date
    Aug 2012
    Location
    Auckland, NZ
    Posts
    1,525
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data to Date Format

    Peter & FormR,

    Am I missing something, why use Substitute? My solution was to use this: =RIGHT(A1,LEN(A1)-FIND("Tgl.",A1)-3)*1 It does assume that 'Tgl.' always precedes the date, but thats what the example shows. The *1 then coerces the string into a date (assuming the cell is appropriately formated).

    Regards
    Peter

    Excel 2016, Windows 10
    Accuracy in posting formula's to the forum will help enormously, clairvoyancy is a skill I've yet to develop!!

  5. #5
    New Member
    Join Date
    Oct 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data to Date Format

    Hi,

    You could also use this formula:
    =DATEVALUE(TRIM(RIGHT(MID(A2,FIND("Tgl.",A2)+4,20),20)))

  6. #6
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Data to Date Format

    Quote Originally Posted by pjmorris View Post
    why use Substitute?
    There are many ways to skin a cat
    [code]your code[/code]

  7. #7
    Board Regular
    Join Date
    Aug 2012
    Location
    Auckland, NZ
    Posts
    1,525
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data to Date Format

    Oh that is certainly true with Excel - I just wondered if there was a particular benefit i'm missing.
    Peter

    Excel 2016, Windows 10
    Accuracy in posting formula's to the forum will help enormously, clairvoyancy is a skill I've yet to develop!!

  8. #8
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Data to Date Format

    Quote Originally Posted by pjmorris View Post
    Oh that is certainly true with Excel - I just wondered if there was a particular benefit i'm missing.
    Peter may have a different view - but I don't think so.. here is another option (saves a function call)

    Sheet2

     AB
    1sample rawAfter
    27143/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.19 November 201219/11/2012
    3100/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.10 January 201210/01/2012
    445/R/DJSDPPI.3/KOMINFO/11/2012 Tgl.26 March 201226/03/2012

    Spreadsheet Formulas
    CellFormula
    B2=MID(A2,FIND("Tgl.",A2)+4,99)*1


    Excel tables to the web >> Excel Jeanie HTML 4
    [code]your code[/code]

  9. #9
    Board Regular
    Join Date
    Aug 2012
    Location
    Auckland, NZ
    Posts
    1,525
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data to Date Format

    Like that; I'd thought about using MiD, but hadn't thought to set the final value to a large value.
    Peter

    Excel 2016, Windows 10
    Accuracy in posting formula's to the forum will help enormously, clairvoyancy is a skill I've yet to develop!!

  10. #10
    Board Regular muhammad susanto's Avatar
    Join Date
    Jan 2013
    Location
    KPKNL Pekanbaru, Riau
    Posts
    1,441
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Data to Date Format

    hi guys, thanks for all........

Some videos you may like

User Tag List

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
  •