Excel oktatóanyag: szöveg-, szám- és dátumcellák felosztása (több oszlopra szétválasztás)

Az Excel használatakor előfordulhat, hogy bizonyos célokra egy cellából több cellára kell felosztania a szöveget. Ez az oktatóanyag három részre oszlik: szövegcellák felosztása, számcellák felosztása és dátumcellák felosztása. Mindegyik rész különböző példákat kínál, amelyek segítenek megtudni, hogyan kell kezelni a felosztási munkát, ha ugyanazzal a problémával találkozik.

1 Szövegcellák felosztása

Ez a rész összegyűjti azokat a helyzeteket, amelyekkel egy szövegcella több oszlopra való felosztása során szembesül, és megadja a megfelelő módszereket.

1. példa Cellák felosztása vesszővel, szóközzel vagy más határolóval

Ha egy szövegcellát több oszlopra szeretne felosztani egy adott határolóval, például vesszővel, szóközzel, kötőjellel és így tovább, alkalmazza az alábbi módszerek egyikét.

Ossza fel a cellákat határolóval a Szöveg oszlopokká funkcióval

A Szöveg oszlopokba Az Excel beépített funkcióját gyakran használják cellák felosztására. Ahogy az alábbi képernyőképen is látható, a Szövegkarakterláncok oszlop celláinak vesszővel történő felosztásához alkalmazza a Szöveg oszlopokhoz funkciót az alábbiak szerint.

1. Válassza ki azt az oszloptartományt, amelyet vesszővel szeretne felosztani, majd kattintson a gombra dátum > Szöveg oszlopokba.

2. Ban,-ben Szöveg konvertálása oszlopok varázslóvá - 1/3. Lépés párbeszédpanelt, tartsa a a Határolójel választógombot választotta ki, majd kattintson a gombra Következő gombot.

3. Ban,-ben Szöveg konvertálása oszlopok varázslóvá - 2/3. Lépés párbeszédpanelen adja meg az igényeinek megfelelő határolót (ebben az esetben csak a Vessző jelölőnégyzet), majd kattintson a gombra Következő gombot.

4. Az utolsó lépés párbeszédpanelén kattintson a gombbal válasszon ki egy cellát az elválasztott szövegek kimenetére, majd kattintson a gombra befejez gombot.

Ezután a kiválasztott tartományban lévő szövegeket vesszővel kettéválasztjuk, és az alábbiak szerint különböző oszlopokba helyezzük.

A cellák felosztása határolóval a képlettel

Az alábbi képletet alkalmazhatja a cellákban lévő szövegek felosztására egy megadott határolóval az Excelben.

Általános képlet

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

érvek

Ezután alkalmazza ezt a képletet.

1. Ahogy az alábbi képernyőképen látható, először létre kell hoznia egy segédsort az 1, 2, 3... számokkal, amelyek különböző cellákban találhatók.

Megjegyzések: Itt az 1, 2, 3... a szöveges karakterlánc első, második és harmadik részkarakterláncát jelenti.

2. Jelöljön ki egy cellát az 1-es számú cella alatt, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép gombot, hogy megkapja a szöveges karakterlánc első részkarakterláncát. Jelölje ki az eredménycellát, húzza át AutoFill fogantyú jobbra és lefelé a többi részstring eléréséhez. Lásd a képernyőképet:

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

Megjegyzések: A képletben „,” az a határoló, amelyet a szöveges karakterlánc felosztására használnak a B5-ben. Igényei szerint módosíthatja őket.

Ossza fel a cellákat határolóval egy csodálatos eszközzel

Itt ajánlja a Kutools az Excel számára'S Hasított sejtek funkció segítségével egyszerűen feloszthatja a cellákat különálló oszlopokra vagy sorokra egy bizonyos határolóval az Excelben.

1. Telepítés után Kutools az Excel számára, válassza ki azt a tartományt, ahol fel szeretné osztani a szöveges karakterláncokat, majd kattintson a gombra Kutools > Egyesítés és felosztás > Hasított sejtek.

2. Ban,-ben Hasított sejtek párbeszédpanelen az alábbiak szerint kell konfigurálnia.

3. A következőben Hasított sejtek párbeszédpanelen jelöljön ki egy cellát az alkarakterláncok kimenetéhez, majd kattintson a gombra OK.

Ezután a részkarakterláncok különböző oszlopokra vagy sorokra vannak osztva a fenti 2. lépésben megadottak alapján.

Oszlopokra osztás:

Felosztás sorokra:

2. példa Ossza fel a cellákat meghatározott hosszúsággal

Ha bizonyos hosszúságú szöveget szeretne felosztani, a következő módszerek tehetnek jót.

A Szöveg oszlopokba funkcióval bizonyos hosszúságú cellákat oszthat fel

A Szöveg oszlopokba funkció biztosítja a Rögzített szélesség lehetőséget, amely segít a kijelölt cellákban lévő szöveges karakterláncok bizonyos hosszúságú felosztásában az Excelben.

Az alábbi képernyőképen látható módon a B5:B9 tartományban lévő szövegek 3 karakterenkénti oszlopokra osztásához a következőket kell tennie.

1. Jelölje ki azokat a cellákat, ahol fel szeretné osztani a szöveges karakterláncokat.

2. kettyenés dátum > Szöveg oszlopokba.

3. Ban,-ben Szöveg konvertálása oszlopokká varázsló – 1/3. lépés párbeszédpanelen válassza a ikont Rögzített szélesség rádiógombot és kattintson rá Következő.

4. Ezután a Az 2 3 lépése megjelenik a párbeszédpanel. Ban,-ben Adatok előnézete szakaszban kattintson a tengely kívánt pozíciójára egy törésvonal (egy nyíllal ellátott vonal) létrehozásához. Az összes törésvonal létrehozása után kattintson a Következő gombot.

Ebben az esetben a szöveges karakterlánc minden 3 karakteréhez törésvonalat készítek.

5. Az utolsó lépés varázslóban válasszon ki egy cellát az elválasztott szövegek kiadásához, majd kattintson a gombra befejez gombot.

Most a kijelölt cellákban lévő szöveges karakterláncok 3 karakterenként fel vannak osztva az alábbi képernyőképen látható módon.

Ossza meg a sejteket bizonyos hosszúsággal egy csodálatos eszközzel

A cellák bizonyos hosszúságú felosztásához az Excelben alkalmazhatja a Hasított sejtek jellemzője Kutools az Excel számára hogy könnyen elvégezzük.

1. Jelölje ki azokat a szöveges karakterlánc cellákat, amelyeket bizonyos hosszúsággal fel szeretne osztani, kattintson a gombra Kutools > Egyesítés és felosztás > Hasított sejtek.

2. Ban,-ben Hasított sejtek párbeszédpanelen az alábbiak szerint kell konfigurálnia.

3. A következőben Hasított sejtek párbeszédpanelen jelöljön ki egy cellát a felosztási szövegek elhelyezéséhez, majd kattintson a gombra OK.

Ezután a kijelölt cellákban lévő szöveges karakterláncokat meghatározott hosszúsággal felosztja, és különböző oszlopokba helyezi.

3. példa Ossza fel a cellákat egy bizonyos szóval

Ahogy az alábbi képernyőképen is látható, a B5:B9 tartományban lévő szöveges karakterláncok teljes „értékesítés” szóval történő felosztásához alkalmazhatja az ebben a részben megadott képleteket.

Az alkarakterlánc beolvasása egy bizonyos szó elé a cellában

A LEFT és a KERESÉS függvényeken alapuló képlet alkalmazása segíthet abban, hogy az alkarakterlánc egy adott szó elé kerüljön egy szöveges karakterláncban.

Általános képlet

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

érvek

1. Jelöljön ki egy üres cellát, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép gombot, hogy az alkarakterlánc az adott szó elé kerüljön. Jelölje ki ezt az eredménycellát, majd húzza át AutoFill fogantyú lefelé, hogy a képletet más cellákra alkalmazza.

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

Szerezze be a részkarakterláncot egy szó után egy cellában

Miután megkapta a részkarakterláncot a szó előtt, az alábbi képletet kell alkalmaznia, hogy a szó utáni részkarakterláncot megkapja.

Általános képlet

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

érvek

1. Jelöljön ki egy üres cellát az első eredménycella (D5) mellett.

2. Másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs. Jelölje ki ezt az eredménycellát, húzza át AutoFill fogantyú le a többi eredmény elérése érdekében.

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

Most a szöveges karakterláncokat egy egész szóval különböző oszlopokra osztotta.

4. példa Cellák felosztása sortöréssel

Ez a rész különböző módszereket mutat be, amelyek segítségével sortöréssel oszthatja fel a szöveges cellákat az Excelben.

Ossza fel a cellákat sortöréssel a Szöveg oszlopokká funkcióval

A Szöveg oszlopokká funkció alkalmazható cellák sortöréssel történő felosztására az Excelben. A következőképpen teheti meg.

1. Válassza ki a cellák tartományát, ahol sortöréssel szeretné felosztani a szövegeket.

2. kettyenés dátum > Szöveg oszlopokba.

3. Ban,-ben Szöveg konvertálása oszlopok varázslóvá - 1/3. Lépés párbeszédpanelen válassza ki a a Határolójel rádió gombot, majd kattintson Következő;

4. Ban,-ben Az 2 3 lépése párbeszédpanelen törölje a jelölést a meglévők közül Elhatárolók kiválasztások, ellenőrizze a Más jelölőnégyzetet, majd nyomja meg a gombot Ctrl + J parancsikon. Látható, hogy csak egy kis pont jelenik meg a szövegdobozban, majd a Adatok előnézete dobozban a szövegeket sortörések osztják szét. Kattints a Következő gombot.

5. Az utolsó lépés varázslóban válassza ki a célcellát az elválasztott szövegek kiadásához, majd kattintson a gombra befejez gombot.

Ezután a kijelölt cellákban lévő szövegek sortöréssel különböző oszlopokra vannak osztva.

A cellák felosztása sortöréssel a képlettel

A következő képletek is segíthetnek a cellák sortöréssel történő felosztásában az Excelben.

Ugyanazt a példát használva, mint a fenti, felosztás után három alsztringet kapunk különböző oszlopokban.

Szerezze be az alkarakterláncot az első sortörés előtt

Először is alkalmazhatunk egy képletet a LEFT és a KERESÉS függvényekkel felosztja az alsztringet a cellában az első sortörés előtt.

Általános képlet

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

érvek

1. Jelöljön ki egy üres cellát (ebben az esetben D5), másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs. Jelölje ki az eredmény cellát, és húzza át AutoFill fogantyú le, hogy az alkarakterláncokat a többi cella első sortörése előtt kapja meg.

=BAL(B5, KERESÉS(CHAR(10),B5,1;1)-XNUMX)

Szerezze be az első és a második sortörés közötti részt

Ha egy cellában az első és a második sortörés közötti részkarakterláncot szeretné elérni, a következő képlet tehet egy szívességet.

Általános képlet

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

érvek

1. Jelöljön ki egy cellát (E5) a D5 mellett, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs. Jelölje ki az eredmény cellát, és húzza át AutoFill fogantyú le, hogy a többi cella első és második sortörése közé kerüljenek az alsztringek.

=KÖZÉP(B5, KERESÉS(CHAR(10),B5) + 1, KERESÉS(CHAR(10),B5,KERESÉS(CHAR(10),B5)+1) - KERESÉS(CHAR(10),B5) - 1 )

A második sortörés után kapja meg a részkarakterláncot

Az első lépés a második sortörés utáni részkarakterlánc beszerzése az alábbi képlettel.

Általános képlet

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

érvek

1. Jelöljön ki egy cellát (ebben az esetben F5), másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs. Jelölje ki az eredmény cellát, és húzza le az Automatikus kitöltési fogantyút, hogy megkapja a többi cella második sortörése utáni részkarakterláncokat.

=JOBBRA(B5,HOSSZÚ(B5) - KERESÉS(CHAR(10), B5, KERESÉS(CHAR(10), B5) + 1))

VBA segítségével sortöréssel ossza fel cellákat

Ez a rész egy VBA-kódot tartalmaz, amely segít a kijelölt cellákban lévő szövegek sortöréssel történő egyszerű felosztásában az Excelben.

1. megnyomni a más + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.

2. Ban,-ben Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modulok. Ezután másolja az alábbi VBA -t a Kód ablakba.

VBA kód: cellák felosztása sortöréssel az Excelben

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. megnyomni a F5 gombot a kód futtatásához. Aztán a Kutools az Excel számára párbeszédpanel jelenik meg, ki kell választania a sortöréssel felosztani kívánt cellatartományt, és végül kattintson a OK gombot.

Ezután a kijelölt cellákban lévő szövegek sortöréssel egyszerre különböző oszlopokra vannak osztva.

Ossza meg a cellákat sortöréssel egy csodálatos eszközzel

Itt mutasson be egy praktikus eszközt - Hasított sejtek hasznossága Kutools az Excel számára. Ezzel az eszközzel a kijelölt cellákban tömegesen, sortöréssel, néhány kattintással feloszthatja a szövegeket.

1. Válassza ki a cellák tartományát, ahol sortöréssel szeretné felosztani a szövegeket.

2. kettyenés Kutools > Egyesítés és felosztás > Hasított sejtek a funkció engedélyezéséhez.

3. Ban,-ben Hasított sejtek párbeszédpanelen, meg kell adnia a következő beállításokat.

4. A következő felbukkanóban Hasított sejtek párbeszédpanelen válasszon ki egy üres cellát az elválasztott szövegek elhelyezéséhez, és kattintson a gombra OK.

Ezután a kijelölt cellákban lévő szövegek sortöréssel különböző oszlopokra vannak osztva az alábbi képernyőképen látható módon.

5. példa A cellák felosztása csak az első vagy az utolsó határolóval

Néha előfordulhat, hogy egy cellát két részre kell osztania, csak az első vagy az utolsó határolóra hivatkozva, próbálkozzon az alábbi képletekkel.

A cellák felosztása az első határolóval képletekkel

Amint az alábbi képernyőképen látható, a B5:B9 tartomány egyes celláinak az első szóközzel két részre osztásához két képletet kell alkalmaznia.

Oszd fel az első szóköz előtti részkarakterláncot

Az első szóköz előtti részkarakterlánc felosztásához használhat egy képletet, amely a LEFT függvényen és a FIND függvényeken alapul.

Általános képlet

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

érvek

1. Válasszon ki egy cellát (ebben az esetben D5) az alkarakterlánc kimenetéhez, másolja be vagy írja be az alábbi képletet, majd nyomja meg a belép kulcs. Válassza ki az eredmény cellát, majd húzza át AutoFill fogantyú le, hogy megkapja a többi cella részkarakterláncát.

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

Oszd fel az első szóköz utáni részkarakterláncot

Ezután alkalmazza az alábbi képletet, hogy a részkarakterláncot a cella első szóköze után kapja meg.

Általános képlet

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

érvek

1. Másolja vagy írja be az alábbi képletet az E5 cellába, és nyomja meg a gombot belép kulcs az eredmény eléréséhez. Jelölje ki ezt az eredménycellát, és húzza át AutoFill fogantyú le, hogy a többi cella utolsó szóköze után legyen az alkarakterlánc.

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

Megjegyzések: A képletekben igény szerint módosíthatja a cellahivatkozást a szöveges karakterláncra és a határolót.

A cellák felosztása az utolsó határolóval képletekkel

Ha a cellatartományban (B5:B9) lévő szövegeket a képernyőképen látható módon az utolsó szóközzel két részre szeretné osztani, az ebben a részben található két képlet segíthet a megvalósításban.

Tegye a szöveget az utolsó határolótól balra

Ha a szöveget egy cellában az utolsó határolótól balra szeretné helyezni, alkalmazza az alábbi képletet.

Általános képlet

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

érvek

1. Válasszon ki egy cellát a bal oldali részkarakterlánc elhelyezéséhez, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs. Jelölje ki az eredmény cellát, és húzza át AutoFill fogantyú lefelé, hogy a képletet más cellákra alkalmazza.

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

Helyezze a szöveget az utolsó határolótól jobbra

Most ebben az esetben az utolsó szóköztől jobbra kell eljuttatnunk a szöveget.

Általános képlet

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

érvek

1. Válasszon ki egy cellát a megfelelő részkarakterlánc elhelyezéséhez, másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs. Jelölje ki az eredménycellát, és húzza le az Automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához.

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

Megjegyzések: A képletekben igény szerint módosíthatja a cellahivatkozást a szöveges karakterláncra és a határolót.

6. példa A cellák felosztása nagybetűvel

Ez a rész egy felhasználó által definiált funkciót mutat be, amellyel a cellákban lévő szavakat nagybetűvel lehet felosztani.

1. megnyomni a más + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.

2. Ban,-ben Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modulok, majd másolja az alábbi VBA kódot a Kód ablakba.

VBA-kód: Cellák felosztása nagybetűkkel az Excelben

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. megnyomni a más + Q gombok a Microsoft Visual Basic for Applications ablak.

4. Jelöljön ki egy cellát (ebben az esetben D5), amelyik az első nagybetűtől balra kiírja a szót, írja be az alábbi képletet, majd nyomja meg a belép kulcs. Válassza ki ezt az eredményt tartalmazó cellát, és húzza át AutoFill fogantyú majd le, hogy megkapja a többi szót.

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

Megjegyzések: A kódban a $B5 a felosztandó cella, a $D:D pedig az eredménycella oszlopa. Kérjük, módosítsa azokat saját adatai szerint.

7. példa Nevek felosztása cellákban

Tételezzük fel, hogy van egy munkalapja, amely egy teljes neveket tartalmazó oszlopot tartalmaz, és a teljes nevet külön oszlopokra szeretné osztani, például a kereszt- és vezetéknevet a teljes névből, vagy a kereszt-, közép- vagy vezetéknevet a teljes névből. Ez a rész részletes lépéseket sorol fel a problémák megoldásához.

Ossza fel a teljes nevet kereszt- és vezetéknévre

Ahogy az alábbi képernyőképen látható, az utónév, a középső név és a vezetéknév egyetlen szóközzel van elválasztva. Ha csak a kereszt- és vezetéknevet szeretné szétválasztani a teljes névből, és különböző oszlopokba szeretné helyezni, alkalmazza az egyik következő módszereket.

1) Ossza fel a teljes nevet kereszt- és vezetéknévre képletekkel

Két képletet külön kell alkalmaznia a teljes név keresztnévre és vezetéknévre való felosztásához.

Vágja szét a keresztnevet teljes névből

Alkalmazhat egy képletet a BAL és a KERESÉS függvények alapján, hogy a keresztnevet teljes névből leválasztja.

Általános képlet

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

1. Jelöljön ki egy cellát a keresztnév kiadásához.

2. Másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs. Válassza ki ezt az eredményt tartalmazó cellát, és húzza át AutoFill fogantyú lefelé a keresztnevek és a többi teljes név elválasztásához.

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

Most már az összes keresztnevet felosztotta a teljes nevek közül a megadott cellatartományban, folytassa az alábbi képlet alkalmazásával a vezetéknevek felosztásához.

Vágja szét a vezetéknevet teljes névből

Általános képlet

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

1. Jelöljön ki egy cellát a keresztnév cella mellett.

2. Másolja be vagy írja be az alábbi képletet, majd nyomja meg a gombot belép kulcs. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltés fogantyúját, hogy a vezetékneveket más teljes nevek közül lekérje.

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

Megjegyzések: A képletekben B5 a felosztani kívánt teljes név cella. Szükség szerint módosíthatja.

2) Ossza meg a teljes nevet kereszt- és vezetéknévre egy csodálatos eszközzel

Sok Excel-felhasználó számára nehéz megjegyezni a képletet. Itt ajánljuk a Osztott nevek jellemzője Kutools az Excel számára. Ezzel a funkcióval egyszerűen, néhány kattintással feloszthatja a teljes nevet kereszt- és vezetéknévre.

1. Válassza ki a teljes névvel rendelkező cellák tartományát, majd kattintson a gombra Kutools > Egyesítés és felosztás > Osztott nevek.

2. Ban,-ben Osztott nevek párbeszédpanelen csak a Keresztnév és a Vezetéknév doboz a Felosztott típusok szakaszra, majd kattintson a gombra OK.

tippek: A kiválasztott tartomány megjelenik a Felosztandó tartomány doboz, szükség szerint módosíthatja.

3. Aztán még egy Osztott nevek párbeszédpanel jelenik meg, válassza ki a célcellát, és kattintson OK.

Ezután a kijelölt cellákban lévő teljes név utó- és vezetéknévre lesz osztva tömegesen, ahogy az alábbi képernyőképen is látható.

A teljes név felosztása keresztnévre, középső és vezetéknévre

Amint az alábbi képernyőképen látható, van egy teljes név oszlop, amelyben a keresztnév, a középső név és a vezetéknév egyetlen szóközzel van elválasztva. A teljes név keresztnévre, középső névre és vezetéknévre való felosztásához és különböző oszlopokba helyezéséhez az alábbi módszerek segíthetnek.

1) Oszd fel a teljes nevet keresztnévre, középső és vezetéknévre a Szöveg oszlopokba funkcióval

A beépített funkció – A szöveg oszlopokba funkció segítségével könnyedén feloszthatja a teljes nevet keresztnévre, középső névre és vezetéknévre az Excelben.

Tudod kövesse a fent említett lépéseket a Szöveg oszlopokba funkció alkalmazásához.

Megjegyzések: A 2/3 varázslóban csak a Hely doboz.

2) Ossza fel a teljes nevet keresztnévre, középső és vezetéknévre képletekkel

Képletekkel is feloszthat egy teljes nevet keresztnévre, középső névre és vezetéknévre az Excelben.

A teljes név felosztására használt általános képletek

Oszd fel a keresztnevet

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

Oszd fel a középső nevet

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

Oszd fel a vezetéknevet

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

Ezután alkalmazza a képleteket a különböző cellákban, hogy megkapja a kereszt-, közép- és vezetéknevet.

1. A D5 cellába írja be az alábbi képletet, és nyomja meg a gombot belép kulcs. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút, hogy megkapja a többi teljes név keresztnevét.

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

2. Írja be az alábbi képletet az E5 cellába, és nyomja meg a gombot belép gombot az első középső név megadásához. Jelölje ki az eredmény cellát, és húzza le az Automatikus kitöltési fogantyút a többi középső név megjelenítéséhez.

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

3. A vezetéknevek lekéréséhez írja be az alábbi képletet az F5 cellába, és nyomja meg a billentyűt belép, majd jelölje ki az eredmény cellát, és húzza át az Automatikus kitöltési fogantyút a szükséges cellák fölé.

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

3) Ossza meg a teljes nevet keresztnévre, középső és vezetéknévre egy csodálatos eszközzel

Itt egy bevezető Kutools az Excel számára'S Osztott nevek funkció, amely lehetővé teszi a teljes név felosztását utónévre, középső névre és vezetéknévre, mindössze néhány kattintással.

1. Jelölje ki a teljes névvel rendelkező cellákat, amelyeket fel szeretne osztani, majd kattintson a gombra Kutools > Egyesítés és felosztás > Osztott nevek.

2. Ban,-ben Osztott nevek párbeszédpanelen az alábbiak szerint kell konfigurálnia.

3. A következő felbukkanóban Osztott nevek párbeszédpanelen válassza ki a célcellát az elválasztott szövegek kiadásához, majd kattintson a gombra OK. Lásd az alábbi bemutatót.

8. példa Szöveg és számok felosztása cellákban

Feltételezve, hogy létezik egy listát a szöveges karakterláncokról, amelyek vegyes szövegeket és számokat tartalmaznak, ahogy az alábbi képernyőképen látható, a szöveg és a számok elválasztásához és különböző oszlopokba helyezéséhez, négy módszert kínálunk az Ön számára.

Szöveg és számok felosztása képletekkel

A következő képletekkel egy cellából két külön cellára oszthatja fel a szöveget és a számokat.

Általános képletek

Szöveg lekérése a cellából

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

Számokat kérni a cellából

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

Ezután alkalmazza ezt a két képletet különböző cellákban, hogy megkapja a külön szöveget és számokat.

1. Jelöljön ki egy üres cellát a lista első szöveges karakterláncának szövegének elhelyezéséhez, másolja vagy írja be az alábbi képletet, majd nyomja meg a belép kulcs. Jelölje ki az eredmény cellát, és húzza le az Automatikus kitöltési fogantyút, hogy a listában szereplő többi szöveges karakterlánc szövege is megjelenjen.

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

2. Jelöljön ki egy cellát (E5) az első eredménycella (D5) mellett, másolja vagy írja be az alábbi képletet, majd nyomja meg a belép. Jelölje ki az eredmény cellát, és húzza le az Automatikus kitöltési fogantyút, hogy megkapja a listában lévő többi szöveges karakterlánc számát.

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

Szöveg és számok felosztása a Flash Fill funkcióval (2013-as és újabb verziók)

Ha Excel 2013-at vagy újabb verziókat használ, alkalmazhatja a Flash töltés beépített szöveg és számok egy cellából két oszlopba történő felosztásához.

Megjegyzések: A Flash Fill funkció működéséhez meg kell keresnie az eredmény oszlopokat az eredeti szöveges karakterlánc oszlopa mellett. Például, ha az eredeti szöveges karakterláncok a B oszlopban találhatók, az elválasztott szövegnek és számoknak a C és D oszlopban kell lenniük. Lásd a képernyőképet:

1. Gépelje be kézzel az első szöveges karakterlánc cella (D5) szövegét a C5 cellába.

2. Továbbra is írja be a második szövegkarakterlánc cella (D6) szövegét a C6 cellába.

3. Aktiválja a C7 cellát, kattintson a gombra dátum > Flash töltés.

Ezután a többi szöveges karakterlánc szövege automatikusan kitöltésre kerül a cellákban, az alábbi képernyőképen látható módon.

4. Ismételje meg az 1–3. lépést, hogy megkapja a D oszlopban lévő számokat.

Megjegyzések:

Szöveg és számok felosztása a felhasználó által definiált funkcióval

Ha egy listában a szöveges karakterláncok nem szabályosak, a fenti két módszer rossz eredményeket adhat vissza, ahogy az az alábbi képernyőképen látható.

Itt bemutatunk egy felhasználó által definiált függvényt, amely segít a szöveg és a számok egy cellából két oszlopra történő felosztásában, függetlenül attól, hogy a számok hol helyezkednek el a szöveges karakterláncban. Lásd a képernyőképet:

1. megnyomni a más + F11 kulcsok.

2. A nyílásban Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modulok, majd másolja az alábbi VBA -t a Kód ablakba.

VBA-kód: Szöveg és számok felosztása egy cellából két oszlopra

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. megnyomni a más + Q gombok a Microsoft Visual Basic for Applications ablak.

4. Jelöljön ki egy cellát az első szöveges karakterlánc szövegének kiadásához, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút, hogy más szöveges karakterláncok szövegét is megkapja ugyanabban a listában.

=SplitText(B5,FALSE)

5. Jelöljön ki egy cellát az első szöveges eredmény cella mellett a számok kiadásához, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút, hogy megkapja a többi szöveges karakterlánc számát.

=SplitText(B5,TRUE)

Szöveg és számok felosztása egy csodálatos eszközzel

Itt ajánljuk a szöveg és a számok egyidejűleg két oszlopra történő felosztásának legegyszerűbb módját. Alkalmazása aHasított sejtek jellemzője Kutools az Excel számára néhány kattintással segít kezelni ezt a problémát.

1. Jelölje ki azokat a szöveges karakterlánc-cellákat, amelyekben a szöveget és a számokat két oszlopra szeretné osztani.

2. kettyenés Kutools > Egyesítés és felosztás > Hasított sejtek.

3. Ban,-ben Hasított sejtek párbeszédpanelen a következő beállításokat kell elvégeznie.

3.1) A kiválasztott tartomány listája a Megosztott tartomány mezőbe, és rákattinthat a gombot egy új tartomány kiválasztásához, ha szükséges;

3.2) A típus szakaszban válassza Felosztás sorokra or Oszlopokra osztás;

3.3) A Felosztva szakaszban válassza ki a szöveg és számát Rádiógomb;

3.4) Kattintson a gombra OK gombot.

4. A következőben Hasított sejtek párbeszédpanelen válasszon ki egy cellát az elválasztott szöveg és számok kiadásához, majd kattintson a gombra OK gombot.

Ezután láthatja, hogy a szöveg és a számok a kijelölt cellákban egyszerre két oszlopra vannak osztva, ahogy az alábbi képernyőképen is látható.

2. Számcellák felosztása

Ez a rész leírja, hogyan lehet a számcellákat felosztani két helyzetben: egynél több számjegyből álló számokat különálló számjegyekre osztani, és a számokat decimálisan felosztani az egész és a tizedes rész elválasztása érdekében.

1. példa: A cellák felosztása számjegyekkel

Ha egy több számjegyből álló számot szeretne felosztani különálló számjegyekre különböző oszlopokban, próbálkozzon az alábbi módszerek egyikével.

Osszon fel egy számot egy cellában egyedi számjegyekre képlet segítségével

Az alábbi képlet segíthet egy szám különálló számjegyekre történő felosztásában és különböző oszlopokba helyezésében.

Általános képlet

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

Érv

1. Válasszon ki egy üres cellát az első számjegy kiadásához, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs.

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

2. Jelölje ki ezt az eredménycellát, és húzza az Automatikus kitöltés fogantyúját jobbra a cellákhoz, hogy további számjegyeket kapjon. Tartsa az összes eredménycellát kijelölve, majd húzza le az Automatikus kitöltés kapcsolót a többi szám egyes számjegyeinek megjelenítéséhez.

Megjegyzések: Ebben a képletben a B3 az a cella, amely az egyes számjegyekre bontandó számot tartalmazza, és szükség szerint módosíthatja.

Egy cellában lévő szám felosztása egyedi számjegyekre a VBA segítségével

Az alábbi VBA-kód abban is segíthet, hogy egy cellában lévő számot egyedi számjegyekre ossza fel az Excelben. A következőképpen teheti meg.

1. megnyomni a más + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.

2. Ban,-ben Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modulok, majd másolja be az alábbi VBA-t a Code ablakba.

VBA-kód: a szám felosztása egyedi számjegyekre az Excelben

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. megnyomni a F5 gombot a kód futtatásához, majd a Kutools az Excel számára párbeszédpanel jelenik meg, ki kell választania a felosztani kívánt számcellák tartományát, majd kattintson a gombra OK gombot.

4. A második Kutools az Excel számára párbeszédpanel jelenik meg, ki kell választania egy cellát az egyes számjegyek kiadásához, majd kattintson a gombra OK.

Megjegyzések: Ez a kód egy szót különálló betűkre is tud osztani.

Ezután a kiválasztott cellákban lévő számokat a rendszer egyes számjegyekre osztja, és különböző oszlopokba helyezi.

Egyszerűen oszthat fel egy számot egyedi számjegyekre egy csodálatos eszközzel

Kutools az Excel számára'S Hasított sejtek A funkció egy praktikus eszköz, amellyel könnyedén feloszthatja a számokat egyedi számjegyekre az Excelben.

1. Telepítés után Kutools az Excel számára, válassza ki a felosztandó számcellák tartományát, majd kattintson Kutools > Egyesítés és felosztás > Hasított sejtek.

2. Ban,-ben Hasított sejtek párbeszédpanelen hajtsa végre a következő beállításokat.

2.1) A Megosztott tartomány részben láthatja a kiválasztott tartományt a szövegmezőben. Kattintson a gomb a tartomány igény szerinti módosításához;

2.2) A típus szakaszban válassza Felosztás sorokra or Oszlopokra osztás igényeinek megfelelően;

2.3) A Felosztva szakaszban válassza ki a Adja meg a szélességet rádió gombot, írja be a számot 1 a szövegmezőbe;

2.4) Kattintson a gombra OK gombot.

3. A nyílásban Hasított sejtek párbeszédpanelen jelöljön ki egy üres cellát a számjegyek kiadásához, majd kattintson a gombra OK gombot.

Ezután a kiválasztott cellákban lévő számok egyszerre lesznek felosztva egyedi számjegyekre.

2. példa Szám felosztása tizedesben

Ez a rész néhány módszert tárgyal a számok egész és decimális részekre való felosztására az Excelben.

A szám felosztása tizedesjegyben képletekkel

A TRUNC függvény használható egy szám tizedesjegyre történő felosztására az Excelben. A következőképpen teheti meg.

Általános képletek

Szerezd meg az egész részt

=TRUNC(A1)

Szerezze meg a tizedes részt

=A1-TRUNC(A1)

Érv

Mostantól ezt a két képletet alkalmazhatja a számok meghatározott cellatartományban tizedesjegyben történő felosztására az Excelben.

1. Válasszon ki egy cellát az első számcella egész részének elhelyezéséhez, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs. Jelölje ki az eredmény cellát, és húzza le az Automatikus kitöltési fogantyút, hogy megkapja a többi számcella egészét.

=TRUNC(B5)

2. Jelöljön ki egy cellát az első eredménycella mellett a tizedes rész elhelyezéséhez, írja be az alábbi képletet, és nyomja meg a gombot belép kulcs. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút, hogy megkapja a többi számcella tizedesjegyeit.

=B5-TRUNC(B5)

A szám felosztása tizedesjegyre a Szöveg oszlopokra funkcióval

Alkalmazhatja a Szöveg oszlopokba funkció a szám tizedesjegyre történő felosztására az Excelben.

1. Válassza ki a tizedesvesszővel felosztandó számcellák tartományát, majd kattintson a gombra dátum > Szöveg oszlopokba.

2. Ban,-ben Szöveg konvertálása oszlopok varázslóvá - 1/3. Lépés párbeszédpanelen válassza a ikont a Határolójel választógombot, és kattintson a gombra Következő gombot.

3. Ban,-ben Az 2 3 lépése párbeszédpanelen csak a Más jelölőnégyzetet, írjon be egy pontot a szövegmezőbe, majd kattintson a Következő gombot.

4. Ban,-ben Az 3 3 lépése párbeszédpanelen kattintson a gombbal válasszon ki egy célcellát a felosztott egész és tizedesjegyek kiadásához, majd kattintson a gombra befejez gomb. Lásd a képernyőképet:

Ezután láthatja, hogy az egész és a tizedes részek fel vannak osztva a kiválasztott cellákból, ahogy az alábbi képernyőképen látható. A tizedes rész azonban elveszti negatív előjelét, ami a felhasználó számára szükséges lehet.

Szám felosztása tizedesben a Flash Fill funkcióval (2013-as és újabb verziók)

A fenti példában bemutattuk a használatát Flash töltés függvény a szöveg és a számok cellákban való felosztására, és itt bevezetjük ugyanazt a módszert a számok tizedesjegyre történő felosztására.

1. Írjon be néhány példát. Ebben az esetben a B5 egész részét írjuk be a C5-be, a B6 egész részét a C6-ba. Lásd a képernyőképet:

Megjegyzések: Negatív számok esetén ne felejtse el együtt beírni a mínuszjelet.

3. Jelölje ki a cellákat, beleértve a néhány példát is a kitölteni kívánt oszlopban, majd kattintson a gombra dátum > Flash töltés.

Ezután az egész számokat kivonja a megadott számú cellákból az alábbi képernyőképen látható módon.

4. Ismételje meg a fenti lépéseket az azonos számú cellák tizedesjegyeinek felosztásához.

3. Dátumcellák felosztása

Egy másik gyakran előforduló helyzet a dátumcellák felosztása. Ha a dátumcellákat külön napra, hónapra és évre kell felosztania, vagy a dátum cellákat külön dátumra és időre kell felosztania, használja a következő megoldást az igényeitől függően.

1. példa Ossza fel a dátumcellákat külön napra, hónapra és évre

Feltéve, hogy van egy dátumlistája a B5:B9 tartományban, és minden dátumértéket három külön oszlopra szeretne felosztani a nap, a hónap és az év számára. Itt három módszert mutatunk be, amelyek segítenek elérni a dátumok felosztásának eredményét.

Ossza fel a dátumcellákat napra, hónapra és évre képletekkel

Három képletet alkalmazhat az alapján DÁTUM funkció, HÓNAP funkció és ÉV függvény a dátum külön napra, hónapra és évre való felosztására az Excelben.

Általános képletek

=DATE(A1)

=MONTH(A1)

=YEAR(A1)

Érv

1. Hozzon létre három oszlopot a külön nap, hónap és év elhelyezéséhez.

2. Válassza ki a Nap oszlop első celláját, írja be az alábbi képletet, és nyomja meg a gombot belép kulcsot az első dátumcella napjának lekéréséhez (B5). Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút a többi dátumcella napjainak megjelenítéséhez.

=DAY(B5)

3. Hajtsa végre az 1. lépésben leírt műveletet a következő képletek alkalmazásához a Hónap és a Év oszlopban, hogy a dátumcellákból megkapja a külön hónapot és évet.

Szerezze meg a randevú hónapját

=MONTH(B5)

Szerezze meg a randevú évét

=YEAR(B5)

Oszd fel a dátumcellákat napra, hónapra és évre a Szöveg oszlopokká funkcióval

Tudod kövesse a fenti lépéseket a Szöveg oszlopokba funkció alkalmazásához a dátumcellák külön napra, hónapra és évre való felosztása az Excelben.

Megjegyzések: Ban,-ben Az 2 3 lépése párbeszédpanelen csak a Más mezőbe, és írja be az a / szimbólum a szövegmezőben.

Ossza fel a dátumcellákat napra, hónapra és évre egy csodálatos eszközzel

Az alábbi bemutató bemutatja, hogyan oszthatja fel a dátumot külön hónapra, napra és évre a Hasított sejtek jellemzője Kutools az Excel számára.

Ez a funkció segít elérni a dátumok tömeges felosztásának eredményét, mindössze néhány kattintással.

Kattintson, ha többet szeretne megtudni erről a szolgáltatásról.

2. példa Dátum és idő felosztása egy cellában

Feltételezve, hogy a B5:B9-ben van egy dátum-idő lista, és a dátumokat és időpontokat külön oszlopokba szeretné osztani, ez a rész különböző módszereket mutat be, amelyek segítségével ezt megteheti.

A dátum és az idő felosztása egy cellában képlettel

A következő képletekkel oszthatja fel a dátumot és az időt az Excel különböző oszlopaira.

1. Készítsen két oszlopot a dátumok és időpontok elhelyezéséhez.

2. Be kell állítani a dátum oszlop celláit találka formátumot, és állítsa be az időoszlop celláit Time formátumban.

3. Jelölje ki az első cellát a találka oszlopban írja be az alábbi képletet, és nyomja meg a gombot belép kulcs a B5 dátumának lekéréséhez. Jelölje ki ezt az eredménycellát, és húzza le az Automatikus kitöltési fogantyút a többi dátum megjelenítéséhez.

=INT(B5)

4. Alkalmazza a következő képletet az idő oszlopban, hogy megkapja a B5:B9 időket.

=B5-D5

Dátum és idő felosztása egy cellában a Flash Fill funkcióval (2013-as és újabb verziók)

Ha az Excel 2013-at és a későbbi verziókat használja, akkor a beépített Flash Fill funkció segítségével különböző oszlopokra oszthatja a dátumot és az időt.

1. Hozza létre a Dátum és Idő oszlopokat, és írjon be néhány példát, amelyeket kimenetként szeretne használni. Ban,-ben találka oszlopban a B5 dátumát írjuk be a C5-be, és a B6 dátumát a C6-ba. Ban,-ben Time oszlopba írjuk be a B5 idejét a D5-be, és a B6 idejét a D6-ba. Lásd a képernyőképet:

2. Válassza ki a Dátum oszlopot (a pár példával együtt), amelyet ki szeretne tölteni, majd kattintson a gombra dátum > Flash töltés.

3. Válassza ki az Idő oszlopot (beleértve a néhány példát is), amelyet ki szeretne tölteni, majd engedélyezze a Flash töltés funkciót is. Ezután a dátumok és időpontok a B5:B9-ben külön oszlopokba vannak osztva, amint az az alábbi bemutatón látható.

Most megtanulta, hogyan oszthat fel cellákat az Excelben különböző módszerekkel különböző helyzetekben. Képes lesz meghozni a legjobb döntést annak meghatározásához, hogy melyik módszert választja, az az adott forgatókönyvtől függ.