Számok eltávolítása az Excelben balról: Ez egyszerű!

Vannak esetek, amikor olyan adatokkal dolgozik, amelyek nem a kívánt módon vannak formázva. Például előfordulhat, hogy egy cellában számok és szövegek keverednek, és ezeket az adatokat meg kell tisztítani.

Számos módszer létezik a számok eltávolítására az Excelben a szöveg bal oldalán. Egyes módszerek akkor működnek, ha az adatok konzisztens mintázattal rendelkeznek. A VBA vagy összetettebb képletek kevésbé strukturált adatokon működnek.

Ez a cikk hét módszert mutat be a szöveges adatok bal oldalán lévő számok törlésére. A végére átfogóan megérti a különböző technikákat és azt, hogy mikor kell használni őket.

Gyerünk!

Tartalomjegyzék

• A Flash Fill használata a számok eltávolítására az Excelben balról
• A Szöveg oszlopokba funkció használata számok eltávolítására
• 5 szöveges függvény a számok eltávolításához az Excelben balról
  • 1. MID funkció
  • 2. Kombinálja a LEN funkcióval
  • 3. A LEFT és a SUBSTITUTE függvények kombinálása
  • 4. A JOBB és LEN funkció használata
  • 5. A REPLACE funkció használata
• A Power Query használata a számok bal oldali eltávolítására
• 3 fejlett technika a karakterek számától függetlenül
  • 1. Kombináljon egy segédoszlopot egy képlettel a karakterek eltávolításához a cellák bal oldalán
  • 2. Hozzon létre egy VBA-makrót a számok eltávolításához
  • 3. Hozzon létre egy felhasználó által meghatározott függvényt
• Végső gondolatok

A Flash Fill használata a számok eltávolítására az Excelben balról

A Flash Fill egy Excel-funkció, amely automatikusan kitölti a mintákat. Ez egy gyors és egyszerű módja annak, hogy eltávolítsa a számokat a cellák bal oldaláról.

Ez a módszer nem mindig működik tökéletesen, de egyszerű esetekben hasznos lehet. A Flash Fill használatához kövesse az alábbi lépéseket:

1. Az adatok melletti üres oszlopba írja be a kívánt eredményt számok nélkül (pl. ha az „1234apple” az A1-es cellában van, írja be az „alma”-t a B1-es cellába).

2. Jelölje ki az új adatot tartalmazó cellát (példánkban B1), majd kattintson a szalag Adatok fülére.

3. Kattintson a Flash Fill gombra az Adateszközök részben.

4. Az Excel automatikusan alkalmazza a mintát, hogy eltávolítsa a számokat a szomszédos cellák bal oldaláról.

Itt található a Flash Fill gomb az Adatok lapon.

Ha inkább parancsikonokat szeretne használni, nyomja meg a CTRL + E billentyűkombinációt Windows rendszeren vagy a CMD + E billentyűkombinációt Mac számítógépen.

A Flash Fill egy praktikus funkció, de mint az Excelben a legtöbb dolog esetében, ugyanaz a feladat többféleképpen is elvégezhető.

A következő részben megnézzük, hogyan teheti meg ugyanezt a „Szöveg az oszlopokba” funkció használatával.

A Szöveg oszlopokba funkció használata számok eltávolítására

Használhatja az Excel „Szöveg oszlopokba” funkcióját a cellák bal oldalán lévő számok elkülönítésére. Ez a módszer azonban azt feltételezi, hogy a cellában lévő számok és szöveg között konzisztens minta vagy határoló van.

Dolgozzunk olyan adatkészlettel, amely kötőjellel választja el a számokat a betűktől, például:

• 1234-alma

• 4321-narancs

• 9876-körte

Ez az előtte-utána, amit szeretnénk elérni:

Íme egy lépésről lépésre útmutató ehhez a mintaadatkészlethez:

1. Válassza ki az A oszlopot.

2. Lépjen az Adatok fülre, és kattintson a Szöveg oszlopokba varázslóra.

3. Válassza a Határozott lehetőséget, majd kattintson a Tovább gombra.

4. Törölje az összes határoló jelét (a tabulátor alapértelmezés szerint alkalmazható).

5. Jelölje be az Egyéb határolót, és írja be a „-“ karaktert a beviteli mezőbe.

6. Válassza ki a megfelelő határolót (szóköz, vessző stb.).

7. Kattintson a Tovább és a Befejezés gombra.

Az adatok most két oszlopra lesznek osztva. Az eltávolított karakterek az első oszlopban, míg a szöveg többi része a második oszlopban találhatók. Ez a kép a határoló beállítását mutatja be:

Ezután vessünk egy pillantást öt szöveges függvényre a számok Excelben való eltávolítására.

5 szöveges függvény a számok eltávolításához az Excelben balról

Ha tudja, hogy hány karakterből áll a számjegy, akkor szöveges funkcióval eltávolíthatja őket.

Tegyük fel, hogy az A1 cella tartalmazza az „ 1234apple ” karakterláncot . Az első négy karakter számok, amelyeket el szeretnénk távolítani.

Számos funkció használható önmagában vagy kombinálva a numerikus karakterek eltávolítására.

1. MID funkció

A MID függvénnyel kivonhatja a karakterlánc 5. karaktertől kezdődő részét.

A MID függvény egy szöveges karakterlánc egy meghatározott szakaszát bontja ki, figyelembe véve a kiindulási pozíciót és a kibontandó karakterek számát. A MID függvény szintaxisa a következő:

MID(szöveg, kezdő_szám, karakterek száma)

• szöveg: A kinyerni kívánt karaktereket tartalmazó szöveg.

• start_num: Az első kinyerni kívánt karakter pozíciója.

• karakterek száma: A szöveges karakterláncból kivonható karakterek száma.

A bal oldali számok eltávolításához kövesse az alábbi lépéseket:

1. válassza ki a B2 cellát, amely tartalmazza a függvényt és az eredményeket.

2. Írja be a képletet: MID(A1, 5, 7)

A fenti képlet eltávolítja a karaktereket a karakterlánc 5. helyéről.

2. Kombinálja a LEN funkcióval

Ennek a módszernek az egyik hátránya, hogy a szöveges karakterlánc nem mindig azonos hosszúságú.

A hosszúság dinamikus kiszámításához használhatja a LEN függvényt. Ez a függvény egy szöveges karakterlánc hosszát adja vissza.

Módosíthatja a képletet úgy, hogy a harmadik paramétert lecserélje a LEN függvényre, így:

=KÖZÉP(A1;5;HOSSZ (A1)-4)

A képletet a kitöltő fogantyúval másolhatja le. Ezen a képen a fenti képlet látható használatban:

3. A LEFT és a SUBSTITUTE függvények kombinálása

A LEFT függvény a szöveges karakterlánc bal szélső karaktereit adja vissza. Például a LEFT("1234apple", 4) az "1234" értéket adja vissza.

Ez akkor hasznos, ha ki szeretné bontani a számokat. De mi van, ha el akarja távolítani őket?

Kombinálhatja a LEFT-t a SUBSTITUTE funkcióval, hogy a számot üres karakterláncra cserélje. Példánknak ez a formátuma:

=SUBSTITUTE(A1, LEFT(A1, 4), "")

4. A JOBB és LEN funkció használata

A RIGHT függvény a szöveges karakterlánc jobb szélső karaktereit adja vissza. A RIGHT függvény szintaxisa a következő:

RIGHT(szöveg, [karakterek száma])

• szöveg: A kinyerni kívánt karaktereket tartalmazó szöveg.

• karakterek_száma: (Opcionális) A karakterlánc jobb oldaláról kivonandó karakterek száma. Ha kimarad, az alapértelmezés szerint 1.

Például a RIGHT(“1234apple”, 5) az „alma” értéket adja vissza.

A RIGHT és LEN funkciókat kombinálhatja is, hogy elkerülje az alfa karakterek számának kemény kódolását. Ez azt feltételezi, hogy ismeri a numerikus karakterek számát.

Példánk formátuma: =JOBB(A1, LEN(A1)-4)

5. A REPLACE funkció használata

A REPLACE funkcióval adott számú karaktert lehet lecserélni egy cellában balról. Ez a szintaxis:

REPLACE(régi_szöveg, kezdő_szám, karakterek száma, új_szöveg)

• old_text: az eredeti szöveges karakterlánc, amelyet módosítani szeretne.

• start_num: az a hely a régi_szövegben, ahol el szeretné kezdeni a karakterek cseréjét.

• karakterek száma: a régi_szövegben lecserélni kívánt karakterek száma.

• new_text: az a szöveges karakterlánc, amellyel a megadott karaktereket le kívánja cserélni.

A következő képlet a mintaadatainkhoz:

=CSERE(A1; 1; 4; "")

És ez az utolsó az öt szöveges függvényünk közül, amelyek segítségével eltávolítjuk a karaktereket a bal oldalról az Excelben.

A legtöbb esetben az általunk eddig ismertetett hét módszer elegendő, de ha nagyobb teljesítményre vágysz, akkor a Power Query a válasz, és erről a következő részben lesz szó!

A Power Query használata a számok bal oldali eltávolítására

A Power Query egy hatékony adatátalakító eszköz , amely elérhető az Excelben. Az ebben a részben bemutatott módszer feltételezi, hogy a cellában lévő számok és szöveg között határoló van.

Az első lépés a céladatok táblázattá alakítása:

1. Lépjen az Adatok fülre.

2. Válassza ki a cellatartományt.

3. Válassza a „Táblázatból/Tartományból” lehetőséget a szalag Adatok lekérése szakaszában.

Amikor megnyílik a Power Query szerkesztő, kövesse az alábbi lépéseket:

1. Válassza ki a céloszlopo(ka)t.

2. Lépjen az „Átalakítás” fülre

3. Kattintson a „Kivonat” elemre a szalagon, és válassza a „Szöveg határoló után” lehetőséget.

4. A „Szöveg határoló után” párbeszédpanelen írjon be egy kötőjelet (-).

5. Kattintson az „OK” gombra a részletek elfogadásához.

Az átalakított oszlopból a szöveg bal oldalán lévő számok eltűnnek.

Kattintson a „Bezárás és betöltés” ​​gombra az átalakított adatok visszatöltéséhez a munkalapra.

Ha még nem ismeri ezt az adateszközt, tekintse meg oktatóanyagunkat itt: .

Ha mélyebben szeretne belemerülni a táblázatok használatába a Power Queryben, ez a videó néhány bonyolultabb példát mutat be:

3 fejlett technika a karakterek számától függetlenül

Az eddigi módszerek fő hátránya, hogy az összes cellában lévő számoknak ugyanazt a mintát kell követniük, vagy meghatározott számú karakterből kell állniuk.

Mi a teendő, ha olyan karaktereket szeretne törölni, amelyek nem tartalmaznak egyértelmű mintát az eredeti adatokban? Az ebben a részben ismertetett technikák középhaladó Excel-felhasználók számára készültek.

1. Kombináljon egy segédoszlopot egy képlettel a karakterek eltávolításához a cellák bal oldalán

Ha nem ismeri a számok teljes hosszát, a függvényeket kombinálhatja a segédoszlopokkal. Ez a módszer kétlépéses folyamatot igényel:

• képlet segítségével hozza létre az eredeti szöveg ideiglenes módosított változatát

• egy másik képlet segítségével távolítsa el a számokat a módosított szöveg alapján.

Az első lépés a következő képlet használatával cserélje ki az összes számot egy adott karakterre (pl. „|”):

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE)(A1, "0", "|)), "1", "|"), "2", "|)),), „3”, „|”), „4”, „|”), „5”, „|”), „6”, „|”), „7”, „|”), „8”, „ |), „9”, „|”)

A második lépés a következő képlet használata egy másik segédoszlopban (pl. C oszlopban):

=TRIM(RIGHT(SUBSTITUTE(B1, “|), REPT(” “, LEN(B1))), LEN(B1)))

Ez az előtte-utána a B és C oszlop képleteivel:

Amikor elkezdi a függvények ilyen kombinálását, problémákba ütközhet, ha rossz beállításokat választ a képletekben. Cikkünk segítséget nyújt egy gyakori hiba elhárításában.

Érdemes lehet olyan táblázatot is megosztani, amely segítő oszlopokat tartalmaz. Fontos, hogy kollégái ne töröljék vagy módosítsák a képleteket a köztes eredményekben. Cikkünk segít a funkcionalitás létrehozásában.

2. Hozzon létre egy VBA-makrót a számok eltávolításához

A VBA egy sokoldalú szkriptnyelv , amely lehetővé teszi a feladatok automatizálását és egyedi megoldások létrehozását az Excelben.

Például írhat egy makrót, amely eltávolítja a számokat egy adott oszlop vagy tartomány összes cellájának bal oldaláról.

Ehhez kövesse az alábbi lépéseket:

1. Nyomja meg az ALT + F11 billentyűt a VBA-szerkesztő megnyitásához.

2. Új modul létrehozásához kattintson a Beszúrás > Modul elemre.

3. Másolja és illessze be a következő kódot, hogy csak a számokat távolítsa el az A oszlop bal oldalán:

Sub RemoveLeftNumbers()
    Dim ws As Worksheet, cell As Range, i As Integer
    
    ' Set the active worksheet as the target worksheet
    Set ws = ActiveSheet

    ' Loop through all the cells in column A
    For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        ' Remove numbers from the left of each cell
        For i = 1 To Len(cell.Value)
            If Not IsNumeric(Mid(cell.Value, i, 1)) Then
                Exit For
            End If
        Next i
        cell.Value = Mid(cell.Value, i)
    Next cell
End Sub

Az utolsó lépés a makró futtatása. Kattintson a zöld háromszögre vagy a „Futtatás” menüpontra.

3. Hozzon létre egy felhasználó által meghatározott függvényt

A VBA segítségével felhasználó által definiált függvényt is létrehozhat, amelyet felhasználhat a képleteiben.

Ehhez kövesse az alábbi lépéseket:

1. Nyomja meg az ALT + F11 billentyűt a VBA-szerkesztő megnyitásához.

2. Új modul létrehozásához kattintson a Beszúrás > Modul elemre.

3. Másolja és illessze be a következő kódot:

Public Function RemoveNumbersFromLeft (text As String) As String
    Dim i As Integer
    For i = 1 To Len(text)
        If Not IsNumeric(Mid(text, i, 1)) Then
            Exit For
        End If
    Next i
    RemoveNumbersFromLeft = Mid(text, i)
End Function

Most térjen vissza az Excel-laphoz, és használja a nyilvános függvényt egy képletben.

Ez a képlet például az A1 cella elfogadásával működik:

=Remove NumbersFrom Left(A1)

Végső gondolatok

Ebben a cikkben több módszert is megvizsgáltunk a számok eltávolítására a szöveges karakterláncok bal oldaláról az Excelben. Mindegyik technikának megvannak a maga előnyei és korlátai.

Nincs egyetlen módszer sem, amely minden helyzetre tökéletes, de biztosan létezik olyan megközelítés, amely a legjobban megfelel az Ön igényeinek. Gyakorlattal jártas lesz ezen technikák alkalmazásában, hogy megbirkózzon bármilyen adatformázási kihívással.

Tehát megvan a lényeg, hogyan távolítsa el a nem kívánt számjegyeket a bal oldalról az Excelben. Elsőre kissé trükkösnek tűnhet, de a megfelelő technikákkal pillanatok alatt irányítani fogod! Boldog számozást!