hogyan kell osztani szöveges string Excel vesszővel, szóköz, karakter vagy maszk

a bemutató elmagyarázza, hogyan kell felosztani a cellákat Excel képletek és a Split Text funkció. Meg fogja tanulni, hogyan kell elválasztani a szöveget vesszővel, szóközzel vagy bármely más határolóval, valamint hogyan lehet a karakterláncokat szövegre és számokra osztani.

a szöveg egy cellából több cellába történő felosztása az a feladat, amellyel minden Excel-felhasználó időnként foglalkozik., Az egyik korábbi cikkünkben arról beszéltünk, hogyan oszthatjuk meg a cellákat az Excelben a szöveg-oszlop funkció, a Flash kitöltés és a nevek megosztása bővítmény használatával. Ma, mi lesz, hogy egy mélyreható pillantást, hogyan lehet osztani húrok képletek segítségével, valamint a Split Text funkció.,

• Hogyan cellák felosztása az Excel használata képletek
  • Split string vessző, kettőspont, kötőjel, dash vagy más elválasztó
  • Split karakterlánc által sortörés
  • Képleteket osztott szöveg, szám,
• Hogyan kell külön sejtek az Osztott Szöveg funkció
  • Split sejt által karakter
  • Split sejt által string
  • Split sejt által maszk (minta)

Hogyan osztott szöveget Excel segítségével képletek

split string Excel, általában használja a BAL, a JOBB vagy a MID függvény kombinációban KERESÉS vagy., Első pillantásra a képletek egy része összetettnek tűnhet, de a logika valójában meglehetősen egyszerű, a következő példák pedig néhány nyomot adnak.

osztott karakterlánc vesszővel, vastagbél, perjel, kötőjel vagy más határoló

a cellák Excelben történő felosztásakor a kulcs a határoló helyzetének megkeresése a szövegláncban. A feladatától függően ezt akár esetérzékeny keresés, Akár esetérzékeny keresés segítségével lehet elvégezni. Miután megvan a határoló pozíciója, használja a jobb, bal vagy középső funkciót a szöveges karakterlánc megfelelő részének kibontásához., A jobb megértés, nézzük meg a következő példát.

tegyük fel, hogy van egy lista SKUs a Tétel-Szín-Méret mintát, majd ketté szeretné osztani a oszlop a 3 külön oszlop:

Hogyan osztott karakterlánc által sortörés az Excel

split szöveg tér, képletek használata hasonló az is bizonyítja, hogy az előző példa. Az egyetlen különbség az, hogy szüksége lesz a Char funkcióra a sortörési karakter ellátásához, mivel nem írhatja be közvetlenül a képletbe.,

tegyük fel, hogy a felosztani kívánt cellák hasonlóak ehhez:

vegye ki az előző példából a képleteket, és cserélje ki a kötőjelet (“-“) a CHAR-ra(10), ahol 10 a Sorfolyam ASCII kódja.

és így néz ki az eredmény:

hogyan oszthatjuk meg a szöveget és a számokat Excel

kezdetben nincs olyan univerzális megoldás, amely minden alfanumerikus karakterláncra működne. A használni kívánt képlet az adott karakterláncmintától függ. Az alábbiakban megtalálja a két közös forgatókönyv képleteit.,

“text + number” minta

osztott karakterlánca van, szöveggel és számokkal kombinálva, ahol egy szám mindig követi a szöveget. Az eredeti karakterláncokat úgy szeretné megtörni, hogy a szöveg és a számok külön cellákban jelenjenek meg, így:

az eredmény két különböző módon érhető el.,

1. módszer: számjegyek és kivonat, hogy sok karakter

a legegyszerűbb módja annak, hogy osztott szöveges karakterlánc, ahol a szám után jön a szöveg Ez:

kivonat számok, akkor keresni a karakterlánc minden lehetséges számot 0-9, hogy a számok teljes, és vissza, hogy sok karakter a végén a karakterlánc.,

az eredeti A2 karakterlánccal a képlet a következő:

=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))

a szöveg kibontásához kiszámítja, hogy hány szöveges karaktert tartalmaz a karakterlánc, kivonva a kibontott számjegyek számát (C2) az eredeti karakterlánc teljes hosszából A2-ben. Ezután a bal oldali funkcióval visszaadja azt a sok karaktert a karakterlánc elejétől.,

=LEFT(A2,LEN(A2)-LEN(C2))

ahol az A2 az eredeti karakterlánc, a C2 pedig a kivont szám, amint az a képernyőképen látható:

2.módszer: az 1. számjegy helyzetét egy

karakterláncban egy alternatív megoldás a következő képletet használná határozza meg a karakterlánc első számjegyének helyzetét:

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

a képlet logikájának részletes magyarázata itt található.,

miután megtalálta az első számjegy helyzetét, nagyon egyszerű bal és jobb képletekkel oszthatja meg a szöveget és a számokat.

a szöveg kibontásához:

=LEFT(A2, B2-1)

Kivonat száma:

=RIGHT(A2, LEN(A2)-B2+1)

ahol A2 az eredeti karakterlánc, a B2 pedig az első szám pozíciója., az első számjegy, akkor beágyazása a MIN képletet, a JOBBRA-BALRA funkciók:

Képlet kivonat szöveg:
=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

Képlet kivonat számok: =RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)

Split string “+ szám szöveg” minta

Ha megosztási sejtek hol jelenik meg a szöveg utáni számot, akkor csomagolja ki a számokat a következő képlet: =LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))

A képlet hasonló, hogy a tárgyalt az előző példában, kivéve, hogy használja a BAL funkció, ahelyett, hogy IGAZ, hogy a szám a bal oldalon a húr.,

Ha a számokat, kivonat szöveg levonva a számjegyek száma a teljes hossza az eredeti string:

=RIGHT(A2,LEN(A2)-LEN(B2))

Amennyiben A2 az eredeti string, valamint B2 a kitermelt száma, amint azt a lenti képen:

így oszthatja meg a karakterláncokat Excel-ben a különböző funkciók különböző kombinációival., Amint látja, a képletek messze nem nyilvánvalóak, ezért érdemes letölteni a mintát Excel Split Cells munkafüzet, hogy közelebbről megvizsgálja őket.

Ha az Excel képletek misztikus csavarjainak kitalálása nem a kedvenc foglalkozása, akkor tetszhet a vizuális módszer az Excel celláinak felosztására, amelyet a bemutató következő része mutat be.,

Hogyan cellák felosztása az Excel a Split Szöveg funkció

Egy alternatív módja, hogy szét egy oszlop az Excel segítségével az Osztott Szöveg a szolgáltatás tartalmazza a Végső Suite for Excel, amely a következő lehetőségeket biztosítja:

• Split sejt által karakter
• Split sejt által string
• Split sejt által maszk (minta)

Hogy a dolgok világosabbá, engedd,”s egy közelebbi pillantást minden lehetőség egyszerre.

Split cells by character

válassza ezt a lehetőséget, amikor meg akarja osztani a cellatartalmat a megadott karakter minden egyes előfordulásakor.,

ebben a példában, hagyjuk, hogy a húr a tétel-Szín-méretű mintát, hogy szoktuk az első része ennek a bemutatónak. Mint talán emlékszel, 3 különböző oszlopra osztottuk őket 3 különböző képlettel. És itt van, hogyan lehet elérni ugyanazt az eredményt 2 gyors lépéseket:

1. feltételezve, hogy Ultimate Suite telepítve, válassza ki a cellákat, hogy osztott, majd kattintson a Split Text ikonra a Ablebits Adatok lapon.,
   
2. Az Osztott Szöveg ablak nyílik meg a jobb oldalon az Excel ablak, a következő:
   • Bővítse a Split által karakter csoportot, majd válassza ki az egyik előre megadott elválasztójelek, vagy írja be bármely más karakter, az Egyéni fiók.
   • válassza ki, hogy a cellákat oszlopokra vagy sorokra osztja-e.
   • tekintse át az eredményt az Előnézet szakaszban, majd kattintson a Megosztás gombra.

Kész! A feladat, amely 3 képletet és 5 különböző funkciót igényelt, most csak néhány másodpercet vesz igénybe, majd egy gombnyomásra kattint.

Split cells by string

Ez az opció lehetővé teszi a karakterláncok elválasztását a karakterek bármely kombinációjával. Technikailag egy karakterláncot részekre oszthat úgy, hogy egy vagy több különböző hordozót használ az egyes részek határaként.,

például, hogy szét egy mondat a conjunctions “, illetve”, valamint a “vagy” bontsa ki a Split által húrok csoport, majd írjuk be a határoló húrok, soronként egyet:

az eredmény, a forrás kifejezést külön minden előfordulása az egyes határoló:

és itt egy másik, valós példa., Tegyük fel, hogy importált egy oszlop dátumokat egy külső forrásból, amely a következőképpen néz ki:

5.1.2016 12:20
5.2.2016 14:50

Ez a formátum nem hagyományos Az Excel számára, ezért a Dátumfunkciók egyike sem ismeri fel a dátum vagy az időelemeket. Ha a napot, hónapot, évet, órát és percet külön cellákba szeretné osztani, írja be a következő karaktereket a karakterláncok szerinti bontásban mezőbe:

• Dot (.,) külön nap, hónap, év,
• Kettőspont (:), hogy külön óra, perc
• Hely külön időpont

Hit a Split gombot, akkor azonnal az eredmény:

cellák Felosztása által maszk (minta)

Elválasztó egy cella által maszk azt jelenti, hogy megosztási egy string alapján egy minta.

Ez az opció nagyon hasznos, ha a homogén húrok listáját egyes elemekre vagy szubsztrátumokra kell felosztani., A szövődmény az, hogy a forrásszöveg nem osztható meg egy adott határoló minden egyes előfordulásakor, csak bizonyos események esetén. A következő példa megkönnyíti a dolgok megértését.

tegyük fel, hogy van egy lista a karakterláncok kivont néhány log file:

amit akarsz, hogy a dátum és az idő, ha van ilyen, hibakód és kivétel részleteit 3 külön oszlopban., Egy szóközt nem lehet határolóként használni, mert vannak szóközök dátum és idő között, amelyeknek egy oszlopban kell megjelenniük, és vannak szóközök a kivétel szövegében, amelyeknek szintén egy oszlopban kell megjelenniük.

a megoldás a következő maszkkal osztja fel a karakterláncot: * ERROR:*Exception: *

ahol a csillag ( * ) tetszőleges számú karaktert jelenít meg.

a colonok (:) szerepelnek a határolók, mert nem akarjuk, hogy megjelenjenek a kapott sejtekben.,

és most bontsa ki a Split by mask részt a Split Text panelen, írja be a maszkot az Enter delimiters mezőbe, majd kattintson a Split:

az eredmény hasonló lesz ehhez: