Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe de datum uit tekstreeksen in Excel te extraheren?

Hoe kunt u in het Excel-werkblad de datum uit tekstreeksen extraheren zoals in het volgende screenshot wordt getoond? In dit artikel zal ik het hebben over een handige formule om het op te lossen.

Extract datum uit tekstreeksen met matrixformule in werkblad


Extract datum uit tekstreeksen met matrixformule in werkblad

Om alleen de datum uit een lijst met tekstreeksen te extraheren, kan de volgende matrixformule u helpen, doe dit als volgt:

1. Voer de onderstaande formule in een lege cel in waar u het resultaat wilt krijgen en druk vervolgens op Enter sleutels samen, en alleen de datum wordt geëxtraheerd als volgende screenshot getoond:

=MID(A2,MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1)),LEN(A2)+1)),LOOKUP(1,0*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2)))) + 1 - MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1)),LEN(A2)+1)))

2. Selecteer vervolgens de formulecel en sleep de vulgreep naar de cellen waarop u deze formule wilt toepassen, en u krijgt de resultaten zoals u nodig hebt, zie screenshot:

  • Notes:
  • In de bovenstaande formule, A2 is de cel die de datum bevat die u wilt extraheren;
  • Als de cel andere getallen bevat, zal deze formule niet correct werken ;
  • De formule kan de datum niet correct extraheren als er meer dan één datum in de tekstreeks staat.

Converteer verschillende niet-standaard datums naar de normale werkelijke datum in Excel

Met de Converteren naar datum nut van Kutools for Excel, kunt u in Excel snel verschillende niet-standaard datums converteren naar normale echte datums. Klik om Kutools voor Excel te downloaden!

Kutools for Excel: met meer dan 300 handige Excel-invoegtoepassingen, gratis te proberen zonder beperking in 30 dagen. Download en probeer nu gratis!


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jorge · 8 months ago
    HI, how can I extract the date from this string

    BRIGHT PINK - PK0040 9/1/2020 5:27:55AM 1
  • To post as a guest, your comment is unpublished.
    Javed · 1 years ago
    Hi, Admin.

    i am unable to extract date from below text.

    RETURNED_INCOMPLETE -> INCOMPLETE JSV appointment confirmed 15/10/2020, PM. Without WFM sub after 5pm
    upon entering the given formula values are coming. 15/10/2020, PM. Without WFM sub after 5

    "RETURNED_INCOMPLETE -> INCOMPLETE JSV-Appointment confirmed on -21/10/2020 PM mhumza wanted 06 to 07 PM coz working"
    upon entering the given formula values are coming. 21/10/2020 PM mhumza wanted 06 to 07

    "RETURNED_INCOMPLETE -> INCOMPLETE JSV-Appointment confirmed on 18/10/2020 PM mhumza wanted at 16:30 to 18:00 pm"
    upon entering the given formula values are coming. 18/10/2020 PM mhumza wanted at 16:30 to 18:00

    RETURNED_INCOMPLETE -> INCOMPLETE JSV appointment confirmed 15/10/2020, AM. Without WFM sub is available only until 9am
    upon entering the given formula values are coming. 15/10/2020, AM. Without WFM sub is available only until 9


    please support and help.
  • To post as a guest, your comment is unpublished.
    rogers · 1 years ago
    Hello!

    How can I extract date from the text "Wed Jul 01 2020 04:20:05 GMT+0000 (Coordinated Universal Time)" in mm/dd/YYYY format using a formula?

    Can someone please help me.

  • To post as a guest, your comment is unpublished.
    Josh · 1 years ago
    I'm aware that the formula wont work if there are other numbers in the cell, however, is there a way to only extract numbers that are in date format?
    Example: People 5/ 2/12/20
    Ignore the 5 and only output the 2/12/2020

    Thank you
  • To post as a guest, your comment is unpublished.
    Adam Tabor · 1 years ago
    This was working perfectly up until 01/01/2020 - Anyone know how to fix this?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Adam,
      The formula has been fixed as below:
      =MID(A2,MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1)),LEN(A2)+1)),LOOKUP(1,0*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2)))) + 1 - MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1)),LEN(A2)+1)))

      Please try, hope it can help you!
      • To post as a guest, your comment is unpublished.
        Adam Tabor · 1 years ago
        skyyang - Sorry I've moved away onto something else. This works perfectly - Thank you so much!
      • To post as a guest, your comment is unpublished.
        Neil · 1 years ago
        I'm experiencing a problem with this formula not displaying the entire date value.
        Similar to Adam Tabor, the formula was displaying the date value as expected up until 01/01/2020. Since then, the date value is missing the last digit

        Example:
        Cell A1 contains the string "Monthly-Returned-Ticket-Report-01-29-2020"

        Cell A2 contains the following formula:
        =MID(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1,1),LEN(A1)+1)),LOOKUP(1,0/MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1)))) + 1 - MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1,1),LEN(A1)+1)))

        Expected Result: Cell A2 displays the value "01-29-2020"

        Actual Result: Cell A2 displays the value "01-29-202"

        Hoping someone has an idea about what needs to be tweaked to deal with this new behavior since the new year?
        • To post as a guest, your comment is unpublished.
          skyyang · 1 years ago
          Hello, Neil,
          The formula in this article has been updated, please apply the below formula:
          =MID(A2,MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1)),LEN(A2)+1)),LOOKUP(1,0*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2)))) + 1 - MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1)),LEN(A2)+1)))

          Please try, hope it can help you!
          • To post as a guest, your comment is unpublished.
            Neil · 1 years ago
            This updated formula worked for my use case when I changed my source cell to A2. Thanks for the update Skkyang! :)
      • To post as a guest, your comment is unpublished.
        Joy · 1 years ago
        Hello, I tried it with a string and it doesn't work
        • To post as a guest, your comment is unpublished.
          Adam Tabor · 1 years ago
          skyyang - Sorry I've moved away onto something else. This works perfectly - Thank you so much!
  • To post as a guest, your comment is unpublished.
    Anett · 2 years ago
    Hi, Help me please! How about if my text is "Date and time of submission:23-Jun-2017 12:34:58 AM PDT. What kind of formula can i use ?
  • To post as a guest, your comment is unpublished.
    Nancy · 2 years ago
    how about if my text is "Date and time of submission: September 16, 2018 at 11:26:00 PM PDT"? What kind of formula can i use ?
  • To post as a guest, your comment is unpublished.
    zgap1122 · 2 years ago
    I'm using Excel 2003, so I believe the IFERROR does not exist, and I found this quoted on the web to be the equivalent:

    IFERROR(A1,"") = IF(ISERROR(A1),"")

    So I'm trying this:

    =MID(A2,MIN(IF(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2,1)),LEN(A2)+1)),LOOKUP(1,0/MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2)))) + 1 - MIN(IF(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2,1)),LEN(A2)+1)))

    - I did press CTRL+SHIFT+ENTER the actual forumla in excel shows it in {}

    Using this formula, I just get a blank result

    Can check and let me know where I’ve gone wrong... Or is it not possible in Excel 2003

    Thank you
    • To post as a guest, your comment is unpublished.
      zgap1122 · 2 years ago
      Actually my data to extract is in the format:

      Data valid for 14 December 2018

      So I need to extract the "14 December 2018" and not the usual
      xx/xx/xx
      • To post as a guest, your comment is unpublished.
        zgap1122 · 2 years ago
        This seemsto for work me(Excel 2003)

        =DATEVALUE(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),17))

        : A2 contains the data to extract

        : it finds the first numerical value, then translates restas date

        Works for data in this format : Data valid for 14 December 2018
        • To post as a guest, your comment is unpublished.
          SGP · 1 years ago
          Hi Tak,
          Its working perfectly. But why "17" at the end? kindly assist.
          • To post as a guest, your comment is unpublished.
            zgap1122 · 1 years ago
            Looking at the "mid" function" it's = number of characters...

            So the longest it will be = "dd september yyyy" = 17 characters

            2 = date
            9 = month
            4 = year
            2 = spaces

            Maybe I should have said the data is : "25 September 2018"

            So 17 should cover for all the months of the year :)
  • To post as a guest, your comment is unpublished.
    SHWETA · 3 years ago
    PLEASE HELP ME EXTRACT DATE FROM THE STATEMENTS LIKE "PLEASE DELIVER BY Fri,01 January ,2016"
  • To post as a guest, your comment is unpublished.
    chathukaperera@gmail.com · 3 years ago
    Can someone help me find a string to extract the date in this format please yyyy-mm-dd
    "2018-03-24T01:42:26-07:00"



    Thanks much in advance