Skip to main content

Conas dáta a bhaint as teaghráin téacs in Excel?

I mbileog oibre Excel, conas a d’fhéadfá an dáta a bhaint as teaghráin téacs mar a leanas an pictiúr a thaispeántar? An t-alt seo, labhróidh mé faoi fhoirmle úsáideach chun í a réiteach.

Dáta sliocht as teaghráin téacs le foirmle eagar sa bhileog oibre

Dáta sliocht as teaghráin téacs le foirmle eagar sa bhileog oibre

Le dáta a bhaint as liosta teaghráin téacs, is féidir leis an bhfoirmle eagar seo a leanas cabhrú leat, déan mar seo:

1. Iontráil an fhoirmle thíos i gcill bhán inar mian leat an toradh a fháil, agus ansin brúigh Iontráil eochracha le chéile, agus ní bhaintear ach an dáta mar a thaispeántar an scáileán a leanas:

=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. Ansin roghnaigh an chill fhoirmle, agus tarraing an láimhseáil líonta síos go dtí na cealla a theastaíonn uait an fhoirmle seo a chur i bhfeidhm, agus gheobhaidh tú na torthaí de réir mar is gá duit, féach an scáileán:

  • nótaí:
  • San fhoirmle thuas, A2 an cill í ina bhfuil an dáta a theastaíonn uait a bhaint;
  • Má tá uimhreacha eile sa chill, ní oibreoidh an fhoirmle seo i gceart ；
  • Ní féidir leis an bhfoirmle dáta a bhaint i gceart cé go bhfuil níos mó ná dáta amháin sa téad téacs.

Tiontaigh dátaí neamhchaighdeánacha éagsúla go gnáthdháta in Excel

Leis an Tiontaigh go dtí seo fóntais de Kutools le haghaidh Excel, is féidir leat dátaí neamhchaighdeánacha éagsúla a thiontú go fíordhátaí gnáth ag an am céanna in Excel. Cliceáil chun Kutools a íoslódáil le haghaidh Excel!

Kutools le haghaidh Excel: le níos mó ná 300 breiseán handy Excel, saor in aisce chun iarracht a dhéanamh gan aon teorainn i 30 lá. Íoslódáil agus triail saor in aisce Anois!

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (26)
No ratings yet. Be the first to rate!
Bibi
Bibi
This comment was minimized by the moderator on the site
Hallo Alle zusammen,

vlt. kann mir jemand helfen. Ich benötige 2 Forme für folgenden text.

RYE6WR / KL 2823 / Belgrade - Amsterdam 08 Nov 2022 07:05 - 08 Nov 2022 09:40 Free Luggage Allowance: 2PC

1. benötige ich eine Formel die mir das Datum filter und wenn möglich in diesem format - dd.mm.jjjj -> in diesem Bsp. 08.11.2022
2. benötige ich eine Formel die mir die erste Uhrzeit filter -> in diesem Bsp. 07:05

Lieben Dnak für all eure Hilfe :)
Reply
0
0
Vivek
Vivek
This comment was minimized by the moderator on the site
i want to extract date from invoice numbers looking like this (114/11-07-2017) i've tried the above formula but it doesn't work, suggest me a formula pls.
Reply
0
0
skyyang
skyyang     Vivek
This comment was minimized by the moderator on the site
Hello, Vivek
May be the below formula can help you:
=TEXT(RIGHT(A2,10),"MM-DD-YYYY")
Pleaase have a try, if you have any other problem, please comment here.
Reply
Report
0
0
Vishal Bhanwal
Vishal Bhanwal
This comment was minimized by the moderator on the site
Hi Usman,

=IFERROR(MID(A29,FIND("/",A29)-2,10),"")

please use this formula, it's very simple and easy to edit. you can edit this formula as per your query.
Reply
0
0
Vishal Bhanwal
Vishal Bhanwal
This comment was minimized by the moderator on the site
=MID(A26,FIND("/",A26)-2,10)

Hey Jorge,

your query is simple and you can get the answer with this formula and also you can get the answer with Flash Fill.
Reply
0
0
Jorge
Jorge
This comment was minimized by the moderator on the site
HI, how can I extract the date from this string
BRIGHT PINK - PK0040 9/1/2020 5:27:55AM 1
Reply
0
0
Javed
Javed
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
rogers
rogers
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
Josh
Josh
This comment was minimized by the moderator on the site
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
Reply
0
0
Adam Tabor
Adam Tabor
This comment was minimized by the moderator on the site
This was working perfectly up until 01/01/2020 - Anyone know how to fix this?
Reply
0
0
skyyang
skyyang     Adam Tabor
This comment was minimized by the moderator on the site
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!
Reply
Report
0
0
Adam Tabor
Adam Tabor     skyyang
This comment was minimized by the moderator on the site
skyyang - Sorry I've moved away onto something else. This works perfectly - Thank you so much!
Reply
0
0
Neil
Neil     skyyang
This comment was minimized by the moderator on the site
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?
Reply
0
0
skyyang
skyyang     Neil
This comment was minimized by the moderator on the site
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!
Reply
Report
0
0
Neil
Neil     skyyang
This comment was minimized by the moderator on the site
This updated formula worked for my use case when I changed my source cell to A2. Thanks for the update Skkyang! :)
Reply
0
0
Joy
Joy     skyyang
This comment was minimized by the moderator on the site
Hello, I tried it with a string and it doesn't work
Reply
0
0
Adam Tabor
Adam Tabor     Joy
This comment was minimized by the moderator on the site
skyyang - Sorry I've moved away onto something else. This works perfectly - Thank you so much!
Reply
0
0
Anett
Anett
This comment was minimized by the moderator on the site
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 ?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations