Bendroji formulė
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Santrauka
Norėdami konvertuoti neatpažinto datos formato tekstą į tinkamą „Excel“ datą, galite analizuoti tekstą ir surinkti tinkamą datą naudodami formulę, pagrįstą keliomis funkcijomis: DATA, LEFT, MID ir RIGHT. Parodytame pavyzdyje formulė C6 yra:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Ši formulė išskiria metų, mėnesio ir dienos reikšmes atskirai ir naudoja DATE funkciją, kad surinktų jas į 2000 m. Spalio 24 d.
Fonas
Kai dirbate su kitos sistemos duomenimis, galite susidurti su situacija, kai datos nėra tinkamai atpažįstamos „Excel“, kuri datas traktuoja kaip tekstą. Pvz., Galite turėti tokių teksto reikšmių:
| Tekstas | Atstovavimo data |
| 20001024 | 2000 m. Spalio 24 d |
| 20050701 | 2011 m. Liepos 1 d |
| 19980424 | 1998 m. Balandžio 24 d |
| 2014.02.28 | 2014 m. Vasario 28 d |
Kai „Excel“ įvertina datos vertę kaip tekstą, viena iš variantų yra naudoti formulę, kad išanalizuotumėte tekstą į jo komponentus (metus, mėnesį, dieną), ir naudokite juos, kad sukurtumėte datą su funkcija DATA. Kaip pažymėta aukščiau, prieš naudodamas formulę, rekomenduoju pirmiausia išbandyti toliau pateiktus sprendimus (pridėti nulį ir naudoti tekstą stulpeliuose). Abi problemos yra greitesnės ir reikalauja mažiau pastangų.
Paaiškinimas
Funkcija DATA sukuria galiojančią datą naudodama tris argumentus: metus, mėnesį ir dieną:
=DATE(year,month,day)
C6 langelyje mes naudojame funkcijas LEFT, MID ir RIGHT, kad išgautume kiekvieną iš šių komponentų iš teksto eilutės, o rezultatus pateikiame į funkciją DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Kairė funkcija ištraukia kairiausius 4 simbolius metams, MID funkcija ištraukia simbolius 5-6 pozicijose mėnesiui, o RIGHT funkcija iš dešiniųjų dešiniųjų simbolių kaip dieną. Kiekvienas rezultatas grąžinamas tiesiai į DATE funkciją. Galutinis rezultatas yra tinkama „Excel“ data, kurią galima suformatuoti bet kokiu būdu.
Šį požiūrį galima pritaikyti pagal poreikį. Pvz., Neatpažintas datos formatas 8 eilutėje yra dd.mm.yyyy ir formulė C8 yra:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Ilgos formos tekstas
Kartais datos gali būti ilgesnės, pvz., „2020 m. Balandžio 11 d. 08:43:13“, kurių „Excel“ tinkamai neatpažįsta. Tokiu atveju galite sugebėti pakoreguoti eilutę taip, kad „Excel“ galėtų tinkamai atpažinti datą naudodama funkciją PAKEISTI. Toliau pateikta formulė antrą tarpo ("") egzempliorių pakeičia kableliu ir tarpu (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Kai pridėsime kablelį po mėnesio pavadinimo, „Excel“ supras datą, tačiau jai vis tiek reikia šiek tiek paspardyti. Todėl pabaigoje pridedame nulį. Dėl matematikos operacijos „Excel“ bando eilutę paversti skaičiumi. Jei tai bus sėkminga, bus nustatyta tinkama „Excel“ data. Atminkite, kad norint tinkamai parodyti datą, gali reikėti pritaikyti datos numerio formatavimą.
Be formulių
Prieš naudodami formulę rankiniu būdu išanalizuoti ir sukonstruoti datą iš teksto, išbandykite vieną iš toliau pateiktų taisymų. Pirmasis variantas naudoja matematikos operaciją, kad „pakreiptų“ „Excel“ ir priverstų ją pabandyti įvertinti tekstą kaip skaičių. Kadangi „Excel“ datos iš tikrųjų yra skaičiai, tai dažnai gali padaryti apgaulę. Jei operacijos pavyks, gali tekti taikyti datos formatą.
Pridėkite nulį, kad pataisytumėte datas
Kartais galite susidurti su datomis teksto formatu, kurį „Excel“ turėtų atpažinti. Tokiu atveju galite priversti „Excel“ konvertuoti teksto reikšmes į datas, prie vertės pridėdami nulį. Kai pridėsite nulį, „Excel“ bandys priversti teksto reikšmes skaičiais. Kadangi datos yra tik skaičiai, ši gudrybė yra puikus būdas konvertuoti datas teksto formatu, kurį „Excel“ tikrai turėtų suprasti.
Norėdami konvertuoti datas į vietą pridėdami nulį, pabandykite Įklijuoti specialiai:
- Į nenaudotą langelį įveskite nulį (0) ir nukopijuokite į mainų sritį
- Pasirinkite problemines datas
- Įklijuoti specialiai> Vertybės> Pridėti
- Taikyti datos formatą (jei reikia)
Taip pat galite pridėti nulį tokioje formulėje:
=A1+0
kur A1 yra neatpažinta data.
Tekstas į stulpelius, kad pataisytumėte datas
Kitas būdas pasiekti, kad „Excel“ atpažintų datas, yra funkcija „Tekstas į stulpelius“:
Pasirinkite datų stulpelį, tada pabandykite Duomenys> Tekstas į stulpelius> Ištaisyta> Baigti
Jei „Excel“ atpažins datas, jas visas pataisys vienu žingsniu.
