Vadnica za Excel: razdelite besedilo, številko in datumske celice (ločite v več stolpcev)

Ko uporabljate Excel, boste morda morali za nekatere namene razdeliti besedilo iz ene celice na več celic. Ta vadnica je razdeljena na tri dele: razdeljene celice z besedilom, razdeljene številske celice in razdeljene celice z datumom. Vsak del ponuja različne primere, ki vam pomagajo vedeti, kako ravnati z delitvijo, ko naletite na isto težavo.

1 Razdelite besedilne celice

V tem delu so zbrane situacije, na katere boste naleteli, ko boste besedilno celico razdelili na več stolpcev, in vam nudi ustrezne metode.

Primer #1 Razdelite celice z vejico, presledkom ali drugimi ločili

Če želite besedilno celico razdeliti na več stolpcev z določenim ločilom, kot so vejica, presledek, pomišljaj in tako naprej, lahko uporabite enega od spodnjih metod.

Razdelite celice z ločilom s funkcijo Besedilo v stolpce

O Besedilo v stolpce funkcija, kot vgrajena Excel, se pogosto uporablja pri delitvi celic. Kot je prikazano na spodnjem posnetku zaslona, ​​lahko za razdelitev celic v stolpcu Besedilni nizi z vejico uporabite funkcijo Besedilo v stolpce, kot sledi.

1. Izberite obseg stolpcev, ki ga želite razdeliti z vejico, kliknite datum > Besedilo v stolpce.

2. V Ljubljani Čarovnik za pretvorbo besedila v stolpce - 1. korak od 3 pogovorno okno, obdržite Razmejeno izbran izbirni gumb in nato kliknite Naslednji gumb.

3. V Ljubljani Čarovnik za pretvorbo besedila v stolpce - 2. korak od 3 pogovornem oknu, določite ločilo glede na vaše potrebe (v tem primeru preverim samo Comma potrditveno polje), nato pa kliknite Naslednji gumb.

4. V pogovornem oknu zadnjega koraka kliknite gumb, da izberete celico za izpis ločenih besedil, nato kliknite Konec gumb.

Nato so besedila v izbranem obsegu razdeljena z vejico in postavljena v različne stolpce, kot je prikazano spodaj.

Celice razdelite z ločilom s formulo

Spodnjo formulo lahko uporabite za razdelitev besedil v celicah z določenim ločilom v Excelu.

Splošna formula

=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

Argumenti

Nato nadaljujte z uporabo te formule.

1. Kot je prikazano na spodnjem posnetku zaslona, ​​morate najprej ustvariti pomožno vrstico s številkami 1, 2, 3..., ki se nahajajo v različnih celicah.

Opombe: Tukaj 1, 2, 3... predstavljajo prvi, drugi in tretji podniz besedilnega niza.

2. Izberite celico pod številko 1, kopirajte ali vnesite spodnjo formulo vanjo in pritisnite Vnesite tipko, da dobite prvi podniz besedilnega niza. Izberite celico z rezultatom, jo ​​povlecite Ročaj za samodejno izpolnjevanje desno in navzdol, da dobite druge podnize. Glej posnetek zaslona:

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(D$4-1)*LEN($B5)+1,LEN($B5)))

Opombe: V formuli ",” je ločilo, ki se uporablja za razdelitev besedilnega niza v B5. Lahko jih spremenite glede na vaše potrebe.

Razdelite celice z ločilom z neverjetnim orodjem

Tukaj priporočite Kutools za ExcelJe Razdeljene celice funkcija, ki vam pomaga enostavno razdeliti celice v ločene stolpce ali vrstice z določenim ločilom v Excelu.

1. Po namestitvi Kutools za Excel, izberite obseg, kjer želite razdeliti besedilne nize, in kliknite Kutools > Spoji in razdeli > Razdeljene celice.

2. V Ljubljani Razdeljene celice pogovorno okno, morate konfigurirati na naslednji način.

3. V naslednjem Razdeljene celice pogovornem oknu, izberite celico za izpis podnizov in kliknite OK.

Nato so podnizi razdeljeni v različne stolpce ali vrstice glede na vaš podatek v 2. koraku zgoraj.

Razdeli na stolpce:

Razdeli na vrstice:

Primer #2 Razdelite celice po določeni dolžini

Če želite besedilni niz razdeliti po določeni dolžini, vam lahko naredijo uslugo naslednje metode.

Razdelite celice po določeni dolžini s funkcijo Besedilo v stolpce

O Besedilo v stolpce funkcija zagotavlja a Fiksna širina možnost, ki vam pomaga razdeliti besedilne nize v izbranih celicah po določeni dolžini v Excelu.

Kot je prikazano na spodnjem posnetku zaslona, ​​lahko za razdelitev besedil v obsegu B5:B9 na stolpce vsake 3 znake naredite naslednje.

1. Izberite celice, kamor želite razdeliti besedilne nize.

2. klik datum > Besedilo v stolpce.

3. V Ljubljani Čarovnik za pretvarjanje besedila v stolpce – korak 1 od 3 v pogovornem oknu izberite Fiksna širina radijski gumb in kliknite Naslednji.

4. Nato Korak 2 3 odpre se pogovorno okno. V Predogled podatkov kliknite na želeni položaj na osi, da ustvarite prelomno črto (črta s puščico). Ko ustvarite vse prelomne vrstice, kliknite na Naslednji gumb za naprej.

V tem primeru ustvarim prelomne vrstice za vsake 3 znake v besedilnem nizu.

5. V čarovniku za zadnji korak izberite celico za izpis ločenih besedil, nato pa kliknite Konec gumb.

Zdaj so besedilni nizi v izbranih celicah razdeljeni na vsake 3 znake, kot je prikazano na spodnjem posnetku zaslona.

Razdelite celice po določeni dolžini z neverjetnim orodjem

Če želite celice razdeliti po določeni dolžini v Excelu, lahko uporabite Razdeljene celice značilnost Kutools za Excel da se to naredi enostavno.

1. Izberite celice besedilnega niza, ki jih želite razdeliti po določeni dolžini, kliknite Kutools > Spoji in razdeli > Razdeljene celice.

2. V Ljubljani Razdeljene celice pogovorno okno, morate konfigurirati na naslednji način.

3. V naslednjem Razdeljene celice pogovornem oknu, izberite celico, v katero želite umestiti delilna besedila, in kliknite OK.

Nato se besedilni nizi v izbranih celicah razdelijo po določeni dolžini in postavijo v različne stolpce.

Primer #3 Razdelite celice z določeno besedo

Kot je prikazano na spodnjem posnetku zaslona, ​​lahko za razdelitev besedilnih nizov v obsegu B5:B9 s celotno besedo »prodaja« uporabite formule, navedene v tem razdelku.

Pridobite podniz pred določeno besedo v celici

Uporaba formule, ki temelji na funkcijah LEFT in FIND, lahko pomaga dobiti podniz pred določeno besedo v besedilnem nizu.

Splošna formula

=LEFT(A1,FIND("certain_word", A1)-1)

Argumenti

1. Izberite prazno celico, kopirajte ali vanjo vnesite spodnjo formulo in pritisnite Vnesite tipko, da dobite podniz pred določeno besedo. Izberite to celico rezultata in jo nato povlecite Ročaj za samodejno izpolnjevanje navzdol, da uporabite formulo za druge celice.

=LEFT(B5,FIND("sales",B5)-1)

Pridobite podniz za besedo v celici

Ko dobite podniz pred besedo, morate uporabiti spodnjo formulo, da dobite podniz za njim.

Splošna formula

=TRIM(MID(A1,SEARCH("certain_word",A1)+LEN("certain_word"),255))

Argumenti

1. Izberite prazno celico poleg prve celice rezultata (D5).

2. Kopirajte ali vnesite spodnjo formulo vanj in pritisnite Vnesite ključ. Izberite to celico rezultata, jo povlecite Ročaj za samodejno izpolnjevanje navzdol, da dobite druge rezultate.

=TRIM(MID(B5,SEARCH("sales",B5)+LEN("sales"),255))

Zdaj ste razdelili besedilne nize v različne stolpce po celotni besedi.

Primer #4 Razdeli celice s prelomom vrstice

Ta razdelek prikazuje različne metode, ki vam pomagajo razdeliti besedilne celice po prelomu vrstice v Excelu.

Razdelite celice po prelomu vrstice s funkcijo Besedilo v stolpce

Funkcijo Besedilo v stolpce lahko uporabite za razdelitev celic s prelomom vrstice v Excelu. To lahko storite na naslednji način.

1. Izberite obseg celic, kjer želite besedila razdeliti s prelomom vrstice.

2. klik datum > Besedilo v stolpce.

3. V Ljubljani Čarovnik za pretvorbo besedila v stolpce - 1. korak od 3 v pogovornem oknu izberite Razmejeno izbirni gumb in nato kliknite Naslednji;

4. V Ljubljani Korak 2 3 pogovornem oknu, počistite vse obstoječe Ločila izbire, preverite Ostalo potrditveno polje in nato pritisnite Ctrl + J bližnjico. Vidite lahko, da je v polju z besedilom prikazana le majhna pika, nato pa v Predogled podatkov polje, so besedila razdeljena s prelomi vrstic. Kliknite na Naslednji gumb.

5. V čarovniku za zadnji korak izberite ciljno celico za izpis ločenih besedil, nato pa kliknite Konec gumb.

Nato so besedila v izbranih celicah razdeljena v različne stolpce s prelomi vrstic.

Razdelite celice s prelomom vrstice s formulo

Naslednje formule lahko pomagajo tudi pri delitvi celic s prelomom vrstice v Excelu.

Z uporabo istega primera kot zgoraj, boste po razdelitvi dobili tri podnize v različnih stolpcih.

Pridobite podniz pred prelomom prve vrstice

Najprej lahko uporabimo formulo, ki temelji na LEVO in ISKANJE funkcije za razdelitev podniza pred prvim prelomom vrstice v celici.

Splošna formula

=LEFT(cell, SEARCH(CHAR(10),cell,1)-1)

Argumenti

1. Izberite prazno celico (v tem primeru D5), kopirajte ali vanjo vnesite spodnjo formulo in pritisnite Vnesite ključ. Izberite celico z rezultatom in jo povlecite Ročaj za samodejno izpolnjevanje navzdol, da dobite podnize pred prelomom prve vrstice drugih celic.

=LEVO(B5, ISKANJE(CHAR(10),B5,1)-1)

Pridobite podniz med prvim in drugim prelomom vrstice

Če želite dobiti podniz med prvo in drugo prelomom vrstice v celici, vam lahko naredi uslugo naslednja formula.

Splošna formula

=MID(cell,SEARCH(CHAR(10),cell)+1,SEARCH(CHAR(10),cell,SEARCH(CHAR(10),cell)+1)-SEARCH(CHAR(10),cell)-1)

Argumenti

1. Izberite celico (E5) poleg D5, kopirajte ali vanjo vnesite spodnjo formulo in pritisnite Vnesite ključ. Izberite celico z rezultatom in jo povlecite Ročaj za samodejno izpolnjevanje navzdol, da dobite podnize med prvim in drugim prelomom vrstice drugih celic.

=MID(B5, ISKANJE(CHAR(10),B5) + 1, ISKANJE(CHAR(10),B5,ISKANJE(CHAR(10),B5)+1) - ISKANJE(CHAR(10),B5) - 1 )

Pridobite podniz po prelomu druge vrstice

Prvi korak je pridobiti podniz po prelomu druge vrstice s spodnjo formulo.

Splošna formula

=RIGHT(cell,LEN(cell) - SEARCH(CHAR(10),cell, SEARCH(CHAR(10), cell) + 1))

Argumenti

1. Izberite celico (v tem primeru F5), kopirajte ali vnesite spodnjo formulo vanjo in pritisnite Vnesite ključ. Izberite celico z rezultatom in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite podnize po drugem prelomu vrstice drugih celic.

=DESNO(B5,LEN(B5) - ISKANJE(CHAR(10), B5, ISKANJE(CHAR(10), B5) + 1))

Razdelite celice po prelomu vrstice z VBA

Ta razdelek vsebuje kodo VBA, ki vam pomaga enostavno razdeliti besedila v izbranih celicah s prelomom vrstice v Excelu.

1. Pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduli. Nato kopirajte spodnji VBA v okno Code.

Koda VBA: razdeli celice s prelomom vrstice v Excelu

Sub ExtendOffice_SplitRangeTex()
'Updated by Extendoffice 20211116
Dim xStr() As String
Dim xRg As Range
Dim xCell As Range
Dim xI As Integer
Set xRg = Application.InputBox("Please select the range of cells where you want to split by line break:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
For xI = 1 To xRg.Count
    Set xCell = xRg.Item(xI)
    xStr = VBA.Split(xCell.Value, vbLf)
    xCell.Resize(1, UBound(xStr) + 1).Offset(0, 1) = xStr
Next
End Sub

3. Pritisnite F5 tipko za zagon kode. Potem a Kutools za Excel Pojavi se pogovorno okno, morate izbrati obseg celic, kjer želite razdeliti s prelomom vrstice, in na koncu kliknite OK gumb.

Nato se besedila v izbranih celicah naenkrat razdelijo v različne stolpce s prelomi vrstic.

Razdelite celice za prelomom vrstice z neverjetnim orodjem

Tukaj predstavite priročno orodje - Razdeljene celice uporabnost Kutools za Excel. S tem orodjem lahko razdelite besedila v izbranih celicah v velikem obsegu s prelomom vrstice z le nekaj kliki.

1. Izberite obseg celic, kjer želite besedila razdeliti s prelomom vrstice.

2. klik Kutools > Spoji in razdeli > Razdeljene celice da omogočite funkcijo.

3. V Ljubljani Razdeljene celice pogovorno okno, morate narediti naslednje nastavitve.

4. V naslednjem pojavnem oknu Razdeljene celice pogovornem oknu, izberite prazno celico, da postavite ločena besedila, in kliknite OK.

Nato so besedila v izbranih celicah razdeljena na različne stolpce s prelomi vrstic, kot je prikazano na spodnjem posnetku zaslona.

Primer #5 Razdelite celice samo po prvem ali zadnjem ločilu

Včasih boste morda morali celico razdeliti na dva dela, ki se nanašajo samo na prvi ali zadnji ločilo, poskusite lahko s spodnjimi formulami.

Celice razdelite s prvim ločilom s formulami

Kot je prikazano na spodnjem posnetku zaslona, ​​morate za razdelitev vsake celice v obsegu B5:B9 na dva dela s prvim presledkom uporabiti dve formuli.

Razdelite podniz pred prvim presledkom

Če želite podniz razdeliti pred prvim presledkom, lahko uporabite formulo, ki temelji na funkciji LEFT in funkcijah FIND.

Splošna formula

=LEFT(cell,FIND("delimiter",cell)-1)

Argumenti

1. Izberite celico (v tem primeru D5), da izpišete podniz, kopirajte ali vnesite spodnjo formulo vanjo in pritisnite Vnesite tipko. Izberite celico z rezultatom in jo povlecite Ročaj za samodejno izpolnjevanje navzdol, da dobite podniz drugih celic.

=LEFT(B5,FIND(" ",B5)-1)

Razdelite podniz za prvim presledkom

Nato uporabite spodnjo formulo, da dobite podniz za prvim presledkom v celici.

Splošna formula

=RIGHT(cell,LEN(cell)-FIND("delimiter",cell))

Argumenti

1. Kopirajte ali vnesite spodnjo formulo v celico E5 in pritisnite Vnesite ključ do rezultata. Izberite to celico rezultata in jo povlecite Ročaj za samodejno izpolnjevanje navzdol, da dobite podniz za zadnjim presledkom drugih celic.

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

Opombe: V formulah lahko spremenite sklicevanje na celico na besedilni niz in ločilo glede na vaše potrebe.

Celice razdelite z zadnjim ločilom s formulami

Če želite besedilo v obsegu celic (B5:B9) razdeliti na dva dela za zadnji presledek, kot je prikazano na posnetku zaslona, ​​vam lahko pri tem pomagata dve formuli v tem razdelku.

Pridobite besedilo levo od zadnjega ločila

Če želite dobiti besedilo levo od zadnjega ločila v celici, lahko uporabite spodnjo formulo.

Splošna formula

=LEFT(B5,SEARCH("^",SUBSTITUTE(cell,"delimiter","^",LEN(cell)-LEN(SUBSTITUTE(cell,"delimiter",""))))-1)

Argumenti

1. Izberite celico, da postavite levi podniz, kopirajte ali vnesite spodnjo formulo vanjo in pritisnite Vnesite ključ. Izberite celico z rezultatom in jo povlecite Ročaj za samodejno izpolnjevanje navzdol, da uporabite formulo za druge celice.

=LEFT(B5,SEARCH("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)

Pridobite besedilo desno od zadnjega ločila

Zdaj moramo v tem primeru spraviti besedilo desno od zadnjega presledka.

Splošna formula

=TRIM(RIGHT(SUBSTITUTE(cell,"delimiter",REPT("delimiter",LEN(cell))),LEN(cell)))

Argumenti

1. Izberite celico, da postavite desni podniz, kopirajte ali vnesite spodnjo formulo vanjo in pritisnite Vnesite ključ. Izberite celico z rezultatom in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da uporabite formulo za druge celice.

=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))),LEN(B5)))

Opombe: V formulah lahko spremenite sklicevanje na celico na besedilni niz in ločilo glede na vaše potrebe.

Primer #6 Razdelite celice z veliko začetnico

Ta razdelek predstavlja uporabniško definirano funkcijo za razdelitev besed v celicah z velikimi črkami.

1. Pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduli, nato kopirajte spodnjo kodo VBA v okno Code.

Koda VBA: razdeli celice z velikimi tiskanimi črkami v Excelu

Function GetName(s As String, Num As Long) As String
'Updated by Extendoffice 20211116
  With CreateObject("VBSCript.RegExp")
    .Global = True
    .Pattern = "[A-Z][a-z]+|[A-Z]"
    GetName = .Execute(s).Item(Num - 1)
  End With
End Function

3. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

4. Izberite celico (v tem primeru D5), da izpišete besedo levo od prve velike črke, vanjo vnesite spodnjo formulo in pritisnite tipko Vnesite tipko. Izberite to celico z rezultati in jo povlecite Ročaj za samodejno izpolnjevanje desno in nato dol, da dobite druge besede.

=IFERROR(GetName($B5,COLUMNS($D:D)),"")

Opombe: V kodi je $B5 celica, ki jo boste razdelili, $D:D je stolpec rezultatne celice. Prosimo, da jih spremenite glede na svoje podatke.

Primer #7 Razdeli imena v celicah

Predpostavimo, da imate delovni list, ki vsebuje stolpec s polnimi imeni, in želite polno ime razdeliti na ločene stolpce, na primer ime in priimek od polnega imena ali razdelitev imena, srednjega ali priimka od polnega imena. V tem razdelku so navedeni podrobni koraki, ki vam bodo pomagali pri reševanju teh težav.

Polno ime razdelite na ime in priimek

Kot je prikazano na spodnjem posnetku zaslona, ​​so ime, srednje ime in priimek ločeni z enim presledkom, da ločite samo ime in priimek od polnega imena in ju postavite v različne stolpce, lahko uporabite enega od naslednje metode.

1) Polno ime razdelite na ime in priimek s formulami

Za razdelitev polnega imena na ime in priimek morate uporabiti dve formuli ločeno.

Razdelite ime od polnega imena

Uporabite lahko formulo, ki temelji na funkcijah LEFT in SEARCH, da ločite ime od polnega imena.

Splošna formula

=LEFT(cell, SEARCH(" ", cell) - 1)

1. Izberite celico za izpis imena.

2. Kopirajte ali vnesite spodnjo formulo vanj in pritisnite Vnesite tipko. Izberite to celico z rezultati in jo povlecite Ročaj za samodejno izpolnjevanje navzdol, da ločite imena od drugih polnih imen.

=LEFT(B5, SEARCH(" ", B5) - 1)

Zdaj ste razdelili vsa imena od polnih imen v določenem obsegu celic, nadaljujte z uporabo spodnje formule za razdelitev priimkov.

Razdelite priimek od polnega imena

Splošna formula

=RIGHT(cell, LEN(cell) - SEARCH("^", SUBSTITUTE(cell," ", "^", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))

1. Izberite celico poleg celice z imenom.

2. Kopirajte ali vnesite spodnjo formulo vanj in pritisnite Vnesite ključ. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite priimke iz drugih polnih imen.

=RIGHT(B5, LEN(B5) - SEARCH("^", SUBSTITUTE(B5," ", "^", LEN(B5) - LEN(SUBSTITUTE(B5, " ", "")))))

Opombe: V formulah je B5 celica s polnim imenom, ki jo želite razdeliti. Po potrebi ga lahko spremenite.

2) Razdelite polno ime na ime in priimek z neverjetnim orodjem

Veliko uporabnikov Excela si je formula težko zapomniti. Tukaj priporočamo Razdeljena imena značilnost Kutools za Excel. S to funkcijo lahko enostavno razdelite polno ime na ime in priimek z le nekaj kliki.

1. Izberite obseg celic s polnim imenom, kliknite Kutools > Spoji in razdeli > Razdeljena imena.

2. V Ljubljani Razdeljena imena pogovorno okno, preverite samo Ime in Priimek polje v Razdeljeni tipi in nato kliknite OK.

nasveti: Izbrani obseg je prikazan v Razpon za razdelitev polje, ga lahko po potrebi spremenite.

3. Potem še eno Razdeljena imena Pojavi se pogovorno okno, izberite ciljno celico in kliknite OK.

Nato bo polno ime v izbranih celicah razdeljeno na ime in priimek v velikem obsegu, kot je prikazano na spodnjem posnetku zaslona.

Polno ime razdelite na ime, srednje ime in priimek

Kot je prikazano na spodnjem posnetku zaslona, ​​​​je stolpec s polnim imenom z imenom, srednjim imenom in priimkom ločeni z enim presledkom. Če želite celotno ime razdeliti na ime, srednje ime in priimek ter jih postaviti v različne stolpce, vam lahko pomagajo spodnje metode.

1) Polno ime razdelite na ime, srednje ime in priimek z besedilom v stolpce

Vgrajena funkcija – Besedilo v stolpce vam lahko pomaga enostavno razdeliti polno ime na ime, srednje ime in priimek v Excelu.

Ti lahko sledite zgornjim korakom, da uporabite funkcijo Besedilo v stolpce.

Opombe: V čarovniku korak 2 od 3 preverite samo Vesolje škatla.

2) Polno ime razdelite na ime, srednje ime in priimek s formulami

Uporabite lahko tudi formule za razdelitev polnega imena na ime, srednje ime in priimek v Excelu.

Generične formule, ki se uporabljajo za razdelitev polnega imena

Razdeli ime

=LEFT(cell,SEARCH(" ", cell)-1)

Razdeli srednje ime

=MID(cell, SEARCH(" ", cell) + 1, SEARCH(" ", cell, SEARCH(" ", cell)+1) - SEARCH(" ", cell)-1)

Razdelite priimek

=RIGHT(cell,LEN(cell) - SEARCH(" ",cell, SEARCH(" ",cell,1)+1))

Nato uporabite formule v različnih celicah, da dobite ime, srednje ime in priimek.

1. V celico D5 vnesite spodnjo formulo in pritisnite Vnesite ključ. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite ime drugih polnih imen.

=LEFT(B5,SEARCH(" ", B5)-1)

2. V celico E5 vnesite spodnjo formulo in pritisnite Vnesite ključ za pridobitev prvega srednjega imena. Izberite celico z rezultatom in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite druga vmesna imena.

=MID(B5, SEARCH(" ", B5) + 1, SEARCH(" ", B5, SEARCH(" ", B5)+1) - SEARCH(" ", B5)-1)

3. Če želite dobiti priimke, vnesite spodnjo formulo v celico F5 in pritisnite Vnesite, nato izberite celico z rezultatom in povlecite njen ročaj samodejnega izpolnjevanja čez celice, ki jih potrebujete.

=RIGHT(B5,LEN(B5) - SEARCH(" ",B5, SEARCH(" ",B5,1)+1))

3) Razdelite polno ime na ime, srednje ime in priimek z neverjetnim orodjem

Tukaj je uvod v Kutools za ExcelJe Razdeljena imena funkcija, ki vam omogoča, da z le nekaj kliki naenkrat razdelite polno ime na ime, srednje ime in priimek.

1. Izberite celice s polnim imenom, kamor želite razdeliti, in kliknite Kutools > Spoji in razdeli > Razdeljena imena.

2. V Ljubljani Razdeljena imena pogovorno okno, morate konfigurirati na naslednji način.

3. V naslednjem pojavnem oknu Razdeljena imena pogovornem oknu, izberite ciljno celico za izpis ločenih besedil, nato kliknite OK. Oglejte si spodnji demo.

Primer #8 Razdelite besedilo in številke v celice

Predpostavimo, da obstaja seznam besedilnih nizov z mešanimi besedili in številkami, kot je prikazano na spodnjem posnetku zaslona, ​​da ločite besedilo in številke in jih postavite v različne stolpce, vam ponujamo štiri metode.

Razdelite besedilo in številke s formulami

Z naslednjimi formulami lahko razdelite besedilo in številke iz ene celice v dve ločeni celici.

Splošne formule

Pridobite besedilo iz celice

=LEFT(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))-1)

Pridobite številke iz celice

=RIGHT(cell,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))+1)

Nato uporabite ti dve formuli v različnih celicah, da dobite ločeno besedilo in številke.

1. Izberite prazno celico, da postavite besedilo prvega besedilnega niza na seznam, kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite ključ. Izberite celico z rezultatom in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite besedilo drugih besedilnih nizov na seznamu.

=LEFT(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))-1)

2. Izberite celico (E5) poleg prve celice rezultata (D5), kopirajte ali vnesite spodnjo formulo in pritisnite Vnesite. Izberite celico z rezultatom in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite številke drugih besedilnih nizov na seznamu.

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

Razdelite besedilo in številke s funkcijo Flash Fill (2013 in novejše različice)

Če uporabljate Excel 2013 ali novejše različice, lahko uporabite Flash Fill vgrajen za razdelitev besedila in številk iz ene celice v dva stolpca.

Opombe: Da bi funkcija Flash Fill delovala, morate poiskati stolpce z rezultati poleg prvotnega stolpca z besedilnim nizom. Če se na primer izvirni besedilni nizi nahajajo v stolpcu B, se ločeno besedilo in številke umestijo v stolpca C in D. Glejte posnetek zaslona:

1. Ročno vnesite besedilo prve celice besedilnega niza (D5) v celico C5.

2. Nadaljujte z vnašanjem besedila druge celice besedilnega niza (D6) v celico C6.

3. Aktivirajte celico C7, kliknite datum > Flash Fill.

Nato se besedila drugih besedilnih nizov samodejno izpolnijo v celice, kot je prikazano na spodnjem posnetku zaslona.

4. Ponovite korake od 1 do 3, da dobite številke v stolpcu D.

Opombe:

Razdelite besedilo in številke z uporabniško definirano funkcijo

Če besedilni nizi na seznamu niso običajni, lahko zgornji dve metodi vrneta napačne rezultate, kot je prikazano na spodnjem posnetku zaslona.

Tukaj predstavite uporabniško definirano funkcijo, ki vam pomaga razdeliti besedilo in številke iz celice v dva stolpca, ne glede na to, kje se številke nahajajo v besedilnem nizu. Glej posnetek zaslona:

1. Pritisnite druga + F11 ključi.

2. Na odprtju Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduli, nato pa kopirajte spodnji VBA v okno Code.

Koda VBA: razdeli besedilo in številke iz celice v dva stolpca

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Update by Extendoffice 20211105
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

4. Izberite celico za izpis besedila prvega besedilnega niza, vnesite spodnjo formulo in pritisnite Vnesite ključ. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite besedilo drugih besedilnih nizov na istem seznamu.

=SplitText(B5,FALSE)

5. Izberite celico poleg prve celice z rezultatom besedila, da izpišete številke, vnesite spodnjo formulo in pritisnite Vnesite ključ. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite številke drugih besedilnih nizov.

=SplitText(B5,TRUE)

Razdelite besedilo in številke z neverjetnim orodjem

Tukaj priporočamo najpreprostejši način za razdelitev besedila in številk naenkrat iz celice v dva stolpca. UporabaRazdeljene celice značilnost Kutools za Excel vam pomaga rešiti to težavo z le nekaj kliki.

1. Izberite celice z besedilnim nizom, kjer želite besedilo in številke razdeliti v dva stolpca.

2. klik Kutools > Spoji in razdeli > Razdeljene celice.

3. V Ljubljani Razdeljene celice pogovorno okno, morate narediti naslednje nastavitve.

3.1) Izbrani obseg je naveden v Razdeljen obseg polje in lahko kliknete na gumb za izbiro novega obsega po potrebi;

3.2) V tip oddelek, izberite Razdeli na vrstice or Razdeli na stolpce;

3.3) V Razdeljeno po izberete Besedilo in številko radijski gumb;

3.4) Kliknite OK gumb.

4. V naslednjem Razdeljene celice pogovornem oknu, izberite celico za izpis ločenega besedila in številk, nato pa kliknite OK gumb.

Nato lahko vidite, da so besedilo in številke v izbranih celicah razdeljene na dva stolpca hkrati, kot je prikazano na spodnjem posnetku zaslona.

2. Razdelite številske celice

Ta del opisuje, kako razdeliti številske celice v dveh situacijah: razdelitev števila z več kot eno števko na posamezne števke in razdelitev števila na decimalko, da se loči celo število in decimalni del.

Primer #1: Razdelite celice po številkah

Če želite število z več kot eno števko razdeliti na posamezno številko v različnih stolpcih, poskusite z eno od spodnjih metod.

Razdelite številko v celici na posamezne števke s formulo

Spodnja formula lahko pomaga razdeliti število na posamezne števke in jih postaviti v različne stolpce.

Splošna formula

=MID($A1, COLUMNS($A$1:A$1), 1)

Prepir

1. Izberite prazno celico za izpis prve številke, vnesite spodnjo formulo in pritisnite Vnesite ključ.

=MID($B3, COLUMNS($B$3:B$3), 1)

2. Izberite to rezultatno celico in povlecite njeno ročico samodejnega izpolnjevanja desno do celic, da dobite druge številke. Naj bodo izbrane vse celice rezultatov, nato pa povlecite ročico samodejnega izpolnjevanja navzdol, da dobite posamezne števke drugih številk.

Opombe: V tej formuli je B3 celica, ki vsebuje številko, ki jo je treba razdeliti na posamezne števke, in jo lahko po potrebi spremenite.

Razdelite številko v celici na posamezne števke z VBA

Spodnja koda VBA lahko pomaga tudi pri razdelitvi števila v celici na posamezne števke v Excelu. To lahko storite na naslednji način.

1. Pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduli, nato pa kopirajte spodnji VBA v okno kode.

Koda VBA: razdelite številko na posamezne števke v Excelu

Sub SplitNumberIntoDigits()
'Updateby Extendoffice 2021118
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Kutools for Excel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
 xInt = InputRng.Row

Application.ScreenUpdating = False

For Each Rng In InputRng
    xValue = Rng.Value
    xRow = (Rng.Row - xInt) + 1
    For I = 1 To VBA.Len(xValue)
        OutRng.Cells(xRow, I).Value = VBA.Mid(xValue, I, 1)
    Next
Next
Application.ScreenUpdating = True
End Sub

3. Pritisnite F5 tipko za zagon kode, nato a Kutools za Excel Pojavi se pogovorno okno, morate izbrati obseg številskih celic, ki jih boste razdelili, in nato kliknite OK gumb.

4. Drugi Kutools za Excel Pojavi se pogovorno okno, morate izbrati celico za izpis posameznih števk in nato klikniti OK.

Opombe: Ta koda lahko razdeli besedo tudi na posamezne črke.

Nato se številke v izbranih celicah razdelijo na posamezne števke in postavijo v različne stolpce.

Preprosto razdelite številko na posamezne števke z neverjetnim orodjem

Kutools za ExcelJe Razdeljene celice funkcija je priročno orodje, ki vam pomaga enostavno razdeliti število na posamezne števke v Excelu.

1. Po namestitvi Kutools za Excel, izberite obseg številskih celic, ki jih boste razdelili, kliknite Kutools > Spoji in razdeli > Razdeljene celice.

2. V Ljubljani Razdeljene celice pogovorno okno, naredite naslednje nastavitve.

2.1) V Razdeljen obseg razdelku, lahko vidite obseg, ki ste ga izbrali, prikazan v polju z besedilom. Lahko kliknete na gumb za spreminjanje obsega, kot ga potrebujete;

2.2) V tip oddelek, izberite Razdeli na vrstice or Razdeli na stolpce glede na vaše potrebe;

2.3) V Razdeljeno po izberete Navedite širino izbirni gumb, vnesite številko 1 v besedilno polje;

2.4) Kliknite OK gumb.

3. Na odprtju Razdeljene celice pogovornem oknu, izberite prazno celico za izpis števk in kliknite OK gumb.

Nato se številke v izbranih celicah naenkrat razdelijo na posamezne števke.

Primer #2 Razdelite število na decimalko

V tem razdelku je obravnavanih nekaj metod za razdelitev števila na cele in decimalne dele v Excelu.

Razdelite število na decimalko s formulami

Funkcijo TRUNC lahko uporabite za razdelitev števila na decimalno v Excelu. To lahko storite na naslednji način.

Splošne formule

Pridobite celo število

=TRUNC(A1)

Pridobite decimalni del

=A1-TRUNC(A1)

Prepir

Zdaj lahko uporabite ti dve formuli za razdelitev številk v določenem obsegu celic z decimalnim številom v Excelu.

1. Izberite celico, v katero želite postaviti celi del prve številske celice, vnesite spodnjo formulo in pritisnite Vnesite ključ. Izberite celico z rezultatom in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite cela števila drugih številskih celic.

=TRUNC(B5)

2. Izberite celico poleg prve celice rezultata, da postavite decimalni del, vnesite spodnjo formulo in pritisnite Vnesite ključ. Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite decimalke drugih številskih celic.

=B5-TRUNC(B5)

Razdelite število na decimalko z besedilom v stolpce

Lahko uporabite Besedilo v stolpce funkcija za razdelitev števila na decimalko v Excelu.

1. Izberite obseg številskih celic, ki jih boste razdelili na decimalno število, in kliknite datum > Besedilo v stolpce.

2. V Ljubljani Čarovnik za pretvorbo besedila v stolpce - 1. korak od 3 v pogovornem oknu izberite Razmejeno izbirni gumb in kliknite Naslednji gumb.

3. V Ljubljani Korak 2 3 pogovorno okno, preverite samo Ostalo potrditveno polje, vnesite piko v besedilno polje in nato kliknite Naslednji gumb.

4. V Ljubljani Korak 3 3 pogovorno okno, kliknite na gumb, da izberete ciljno celico za izpis deljenih celih in decimalnih številk, in na koncu kliknite Konec . Oglejte si posnetek zaslona:

Nato lahko vidite, da so celoštevilski in decimalni deli razdeljeni iz izbranih celic, kot je prikazano na spodnjem posnetku zaslona. Vendar decimalni del izgubi svoj negativni predznak, ki je morda potreben za uporabnika.

Razdelite število na decimalko s Flash Fill (2013 in novejše različice)

V zgornjem primeru smo predstavili uporabo Flash Fill funkcijo za razdelitev besedila in številk v celice, tukaj pa bomo uvedli isto metodo za delitev števila na decimalno število.

1. Vnesite nekaj primerov. V tem primeru v C5 vnesemo celo število B5, v C6 vnesemo celi del B6. Glej posnetek zaslona:

Opombe: Za negativna števila ne pozabite skupaj vnesti znaka minus.

3. Izberite celice, vključno z nekaj primeri v stolpcu, ki ga želite izpolniti, pojdite na klik datum > Flash Fill.

Nato se iz določenih številskih celic izvlečejo cela števila, kot je prikazano na spodnjem posnetku zaslona.

4. Ponovite zgornje korake, da razdelite decimalke iz celic z istim številom.

3. Razdelite datumske celice

Druga pogosta situacija je razdelitev datumskih celic. Če morate celice z datumom razdeliti na ločen dan, mesec in leto ali razdeliti celice z datumom na ločen datum in uro, uporabite naslednjo rešitev glede na vaše potrebe.

Primer #1 Razdelite datumske celice na ločen dan, mesec in leto

Ob predpostavki, da imate seznam datumov v obsegu B5:B9 in želite vsako vrednost datuma razdeliti na tri ločene stolpce za dan, mesec in leto. Tukaj so trije načini, ki vam bodo pomagali doseči rezultat delitve datumov.

Razdelite datumske celice na dan, mesec in leto s formulami

Uporabite lahko tri formule na podlagi DATA funkcijo, MESEC funkcijo in LETO funkcija za razdelitev datuma na ločen dan, mesec in leto v Excelu.

Splošne formule

=DATE(A1)

=MONTH(A1)

=YEAR(A1)

Prepir

1. Ustvarite tri stolpce, da postavite ločen dan, mesec in leto.

2. Izberite prvo celico v stolpcu Dan, vnesite spodnjo formulo in pritisnite Vnesite ključ, da dobite dan prve celice za datum (B5). Izberite to rezultatno celico in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite dneve drugih datumskih celic.

=DAY(B5)

3. Izvedite enako operacijo kot v 1. koraku, da uporabite naslednje formule v mesec in Leto stolpec, da dobite ločen mesec in leto iz datumskih celic.

Pridobite mesec zmenka

=MONTH(B5)

Pridobite letnico zmenka

=YEAR(B5)

Razdelite datumske celice na dan, mesec in leto z besedilom v stolpce

Ti lahko sledite zgornjim korakom, da uporabite funkcijo Besedilo v stolpce če želite v Excelu razdeliti datumske celice na ločen dan, mesec in leto.

Opombe: V Korak 2 3 pogovorno okno, preverite samo Ostalo polje in vnesite a / simbol v besedilnem polju.

Razdelite datumske celice na dan, mesec in leto z neverjetnim orodjem

Spodnji demo prikazuje, kako razdeliti datum na ločen mesec, dan in leto z Razdeljene celice značilnost Kutools za Excel.

Ta funkcija vam pomaga doseči rezultat razdelitve datumov v velikem obsegu z le nekaj kliki.

Kliknite, če želite izvedeti več o tej funkciji.

Primer #2 Razdelite datum in čas v celici

Predpostavimo, da je v B5:B9 seznam datumov in ur in želite datume in ure razdeliti v ločene stolpce, bo ta razdelek prikazal različne načine, kako vam to pomaga.

Razdelite datum in čas v celici s formulo

Za razdelitev datuma in časa v različne stolpce v Excelu lahko uporabite naslednje formule.

1. Pripravite dva stolpca za datume in ure.

2. Celice stolpca z datumom morate nastaviti na Datum format in nastavite celice časovnega stolpca na čas format.

3. Izberite prvo celico v Datum stolpec, vnesite spodnjo formulo in pritisnite Vnesite ključ za pridobitev datuma B5. Izberite to celico rezultata in povlecite njen ročaj samodejnega izpolnjevanja navzdol, da dobite druge datume.

=INT(B5)

4. Uporabite naslednjo formulo v časovnem stolpcu, da dobite čase v B5:B9.

=B5-D5

Razdelite datum in čas v celici s funkcijo Flash Fill (2013 in novejše različice)

Če uporabljate Excel 2013 in novejše različice, lahko uporabite vgrajeno funkcijo Flash Fill, da razdelite datum in čas v različne stolpce.

1. Ustvarite stolpca Datum in Čas in vnesite nekaj primerov, ki jih želite kot izhod. V Datum stolpec, v C5 vnesemo datum B5, v C6 pa datum B6. V čas stolpec, vpišemo čas B5 v D5 in vpišemo čas B6 v D6. Glej posnetek zaslona:

2. Izberite stolpec Datum (vključno z nekaj primeri), ki ga želite izpolniti, kliknite datum > Flash Fill.

3. Izberite stolpec Čas (vključno z nekaj primeri), ki ga želite izpolniti, in nato omogočite Flash Fill tudi funkcija. Nato so datumi in časi v B5:B9 razdeljeni v ločene stolpce, kot je prikazano v predstavitvi spodaj.

Zdaj ste se naučili, kako razdeliti celice v Excelu z različnimi metodami v različnih situacijah. Boste lahko sprejeli najboljšo odločitev, da določite, katero metodo izberete, odvisno od vašega specifičnega scenarija.