Jak odstranit čísla v Excelu zleva: Je to snadné!

Jsou chvíle, kdy pracujete s daty, která nejsou naformátována tak, jak potřebujete. Například můžete mít v buňce kombinaci čísel a textu a potřebujete tato data vyčistit.

Existuje několik technik, jak odstranit čísla v Excelu z levé části textu. Některé metody fungují, když mají data konzistentní vzor. VBA nebo složitější vzorce fungují na méně strukturovaných datech.

Tento článek ukazuje sedm metod odstranění čísel nalevo od textových dat. Na konci budete mít komplexní pochopení různých technik a kdy je použít.

Pojďme!

Obsah

• Jak používat Flash výplň k odstranění čísel v Excelu zleva
• Jak používat funkci Text do sloupců k odstranění čísel
• 5 Textové funkce pro odstranění čísel v Excelu zleva
  • 1. Funkce MID
  • 2. Zkombinujte s funkcí LEN
  • 3. Kombinace funkcí LEFT a SUBSTITUTE
  • 4. Použití funkcí RIGHT a LEN
  • 5. Pomocí funkce REPLACE
• Jak používat Power Query k odstranění čísel zleva
• 3 pokročilé techniky bez ohledu na počet znaků
  • 1. Zkombinujte pomocný sloupec se vzorcem pro odstranění znaků z levé části buněk
  • 2. Vytvořte makro VBA pro odstranění čísel
  • 3. Vytvořte uživatelem definovanou funkci
• Závěrečné myšlenky

Jak používat Flash výplň k odstranění čísel v Excelu zleva

Flash Fill je funkce Excelu, která dokáže automaticky vyplnit vzory za vás. Je to rychlý a snadný způsob, jak odstranit čísla z levé strany buněk.

Tato metoda nemusí vždy fungovat dokonale, ale v jednoduchých případech může být užitečná. Chcete-li použít Flash Fill, postupujte takto:

1. Do prázdného sloupce vedle vašich dat zadejte požadovaný výsledek bez čísel (např. pokud je „1234apple“ v buňce A1, zadejte „apple“ do buňky B1).

2. Vyberte buňku s novými daty (v našem příkladu B1) a klikněte na kartu Data na pásu karet.

3. Klikněte na tlačítko Flash Fill v části Data Tools.

4. Excel automaticky použije vzor k odstranění čísel z levé strany sousedních buněk.

Toto je umístění tlačítka Flash Fill na kartě Data.

Pokud dáváte přednost používání zkratek, stiskněte CTRL + E na Windows nebo CMD + E na Macu.

Flash Fill je užitečná funkce, ale stejně jako u většiny věcí v Excelu existuje několik způsobů, jak provést stejný úkol.

V další části se podíváme na to, jak můžete udělat totéž pomocí funkce „Text to Columns“.

Jak používat funkci Text do sloupců k odstranění čísel

K oddělení čísel z levé části buňky můžete použít funkci „Text do sloupců“ v Excelu. Tato metoda však předpokládá, že mezi čísly a textem v buňce existuje konzistentní vzor nebo oddělovač.

Pojďme pracovat se sadou dat, která používá pomlčku k oddělení čísel od písmen, jako je tato:

• 1234-jablko

• 4321-oranžová

• 9876-hruška

Toto je před a po, čeho chceme dosáhnout:

Zde je podrobný průvodce s tímto vzorovým souborem dat:

1. Vyberte sloupec A.

2. Přejděte na kartu Data a klikněte na průvodce Text to Columns.

3. Vyberte možnost Oddělovač a klikněte na Další.

4. Zrušte zaškrtnutí všech oddělovačů (záložka může být použita ve výchozím nastavení).

5. Zaškrtněte Jiný oddělovač a do vstupního pole napište „-“.

6. Vyberte vhodný oddělovač (mezera, čárka atd.).

7. Klepněte na tlačítko Další a Dokončit.

Data budou nyní rozdělena do dvou sloupců. Odebrané znaky jsou v prvním sloupci, zatímco zbytek textu je ve druhém sloupci. Tento obrázek ukazuje, jak nastavit oddělovač:

Dále se podíváme na pět textových funkcí pro odstranění čísel v Excelu.

5 Textové funkce pro odstranění čísel v Excelu zleva

Pokud víte, kolik znaků tvoří číslice, můžete je odstranit pomocí textové funkce.

Předpokládejme, že buňka A1 obsahuje řetězec „ 1234apple “. První čtyři znaky jsou čísla, která chceme odstranit.

Existuje několik funkcí, které lze použít samostatně nebo kombinovat pro odstranění znaků, které jsou číselnými hodnotami.

1. Funkce MID

Pomocí funkce MID můžete extrahovat část řetězce, která začíná 5. znakem.

Funkce MID extrahuje konkrétní část textového řetězce s ohledem na počáteční pozici a počet znaků, které se mají extrahovat. Syntaxe funkce MID je:

MID(text; počáteční_číslo; počet_znaků)

• text: Textový řetězec obsahující znaky, které chcete extrahovat.

• start_num: Pozice prvního znaku, který chcete extrahovat.

• num_chars: Počet znaků, které se mají extrahovat z textového řetězce.

Chcete-li odstranit čísla vlevo, postupujte takto:

1. vyberte buňku B2, která bude obsahovat funkci a výsledky.

2. Zadejte vzorec: MID(A1; 5; 7)

Výše uvedený vzorec odstraní znaky z 5. slotu v řetězci.

2. Zkombinujte s funkcí LEN

Jednou nevýhodou této metody je, že váš textový řetězec nemusí mít vždy stejnou délku.

K dynamickému výpočtu délky můžete použít funkci DÉLKA. Tato funkce vrací délku textového řetězce.

Vzorec můžete upravit tak, aby byl třetí parametr nahrazen funkcí LEN takto:

=MID(A1; 5; DÉLKA(A1)-4)

Vzorec můžete zkopírovat dolů pomocí úchytu výplně. Tento obrázek ukazuje použitý vzorec výše:

3. Kombinace funkcí LEFT a SUBSTITUTE

Funkce LEFT vrací znaky nejvíce vlevo z textového řetězce. Například LEFT(“1234apple”, 4) vrátí hodnotu “1234”.

To je užitečné, když chcete extrahovat čísla. Ale co když je chcete odstranit?

Můžete kombinovat LEFT s funkcí SUBSTITUTE a nahradit tak číslo prázdným řetězcem. Toto je formát pro náš příklad:

=SUBSTITUTE(A1; LEFT(A1; 4); “”)

4. Použití funkcí RIGHT a LEN

Funkce RIGHT vrací znaky nejvíce vpravo z textového řetězce. Syntaxe funkce RIGHT je:

RIGHT(text; [počet_znaků])

• text: Textový řetězec obsahující znaky, které chcete extrahovat.

• num_chars: (Volitelné) Počet znaků, které se mají extrahovat z pravé strany textového řetězce. Pokud je vynechán, výchozí hodnota je 1.

Například RIGHT("1234apple", 5) vrátí hodnotu "apple".

Můžete také kombinovat funkce RIGHT a LEN, abyste se vyhnuli tvrdému kódování počtu alfa znaků. To za předpokladu, že znáte počet číselných znaků.

Formát pro náš příklad je: =RIGHT(A1, LEN(A1)-4)

5. Pomocí funkce REPLACE

Funkci REPLACE lze použít k nahrazení určitého počtu znaků zleva v buňce. Toto je syntaxe:

REPLACE(starý_text, počáteční_číslo, počet_znaků, nový_text)

• old_text: původní textový řetězec, který chcete upravit.

• start_num: pozice ve starém_textu, kde chcete začít nahrazovat znaky.

• num_chars: počet znaků ve starém_textu, které chcete nahradit.

• nový_text: textový řetězec, kterým chcete nahradit zadané znaky.

Následující vzorec je pro naše ukázková data:

=REPLACE(A1; 1; 4; “”)

A to je poslední z našich pěti textových funkcí pro odstranění znaků zleva v Excelu.

Ve většině případů by mělo stačit sedm metod, které jsme dosud probrali, ale pokud chcete větší výkon, řešením je Power Query, a to je to, o čem budeme hovořit v další části!

Jak používat Power Query k odstranění čísel zleva

Power Query je výkonný nástroj pro transformaci dat dostupný v Excelu. Metoda uvedená v této části předpokládá, že mezi čísly a textem v buňce je oddělovač.

Prvním krokem je transformace cílových dat do tabulky:

1. Přejděte na kartu Data.

2. Vyberte rozsah buněk.

3. Vyberte „Z tabulky/rozsahu“ v části Získat data na pásu karet.

Když se otevře Editor Power Query, postupujte takto:

1. Vyberte cílové sloupce.

2. Přejděte na kartu „Transformace“.

3. Klikněte na „Extrahovat“ na pásu karet a vyberte „Text za oddělovačem“.

4. V dialogovém okně „Text za oddělovačem“ zadejte pomlčku (-).

5. Kliknutím na „OK“ přijmete podrobnosti.

Transformovaný sloupec bude mít nyní čísla z levé části textu.

Kliknutím na „Zavřít a načíst“ načtěte transformovaná data zpět do svého listu.

Pokud s tímto datovým nástrojem začínáte, podívejte se na náš výukový program na .

Pokud se chcete hlouběji ponořit do používání tabulek v Power Query, toto video vás provede několika složitějšími příklady:

3 pokročilé techniky bez ohledu na počet znaků

Hlavní nevýhodou dosavadních metod je, že čísla ve všech buňkách musí odpovídat stejnému vzoru nebo musí jít o určitý počet znaků.

Co když chcete odstranit znaky bez jasného vzoru v původních datech? Techniky v této části jsou pro středně pokročilé uživatele Excelu.

1. Zkombinujte pomocný sloupec se vzorcem pro odstranění znaků z levé části buněk

Pokud neznáte celkovou délku čísel, můžete kombinovat funkce s pomocnými sloupci. Tato metoda vyžaduje dvoufázový proces:

• použijte vzorec k vytvoření dočasné upravené verze původního textu

• použijte jiný vzorec k odstranění čísel na základě upraveného textu.

Prvním krokem je použití následujícího vzorce k nahrazení všech čísel určitým znakem (např. „|“):

=NÁHRADIT(NÁHRADIT(NÁHRADIT(NÁHRADIT(NÁHRADIT)(NÁHRADIT(NÁHRADIT)(NÁHRADIT(NÁHRADIT(A1, “0”, “|”), “1”, “|”), “2”, “|”), "3", "|"), "4", "|"), "5", "|"), "6", "|"), "7", "|"), "8", " |"), "9", "|")

Druhým krokem je použití následujícího vzorce v jiném pomocném sloupci (např. ve sloupci C):

=TRIM(RIGHT(SUBSTITUTE(B1; “|”, REPT(” “, DÉLKA(B1))), DÉLKA(B1)))

Toto je před-a-po se vzorci ve sloupcích B a C:

Když začnete takto kombinovat funkce, můžete narazit na problémy s výběrem nesprávných možností ve vzorcích. Náš článek na vám pomůže vyřešit běžnou chybu.

Můžete také chtít sdílet tabulku, která obsahuje pomocné sloupce. Je důležité, aby vaši kolegové nemazali ani neupravovali vzorce v mezivýsledcích. Náš článek o vám pomůže vytvořit funkcionalitu.

2. Vytvořte makro VBA pro odstranění čísel

VBA je všestranný skriptovací jazyk, který umožňuje automatizovat úlohy a vytvářet vlastní řešení v Excelu.

Můžete například napsat makro, které odebere čísla nalevo od všech buněk v konkrétním sloupci nebo rozsahu.

Chcete-li tak učinit, postupujte takto:

1. Stisknutím ALT + F11 otevřete editor VBA.

2. Klepnutím na Vložit > Modul vytvořte nový modul.

3. Zkopírujte a vložte následující kód, abyste odstranili pouze čísla z levé části sloupce A:

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

Posledním krokem je spuštění makra. Klikněte na zelený trojúhelník nebo na položku nabídky „spustit“.

3. Vytvořte uživatelem definovanou funkci

Můžete také vytvořit uživatelsky definovanou funkci pomocí jazyka VBA, kterou můžete použít ve svých vzorcích.

Chcete-li tak učinit, postupujte takto:

1. Stisknutím ALT + F11 otevřete editor VBA.

2. Klepnutím na Vložit > Modul vytvořte nový modul.

3. Zkopírujte a vložte následující kód:

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

Nyní se vraťte do svého listu Excel a použijte veřejnou funkci ve vzorci.

Například tento vzorec funguje tak, že přijme buňku A1:

=RemoveNumbersFromLeft(A1)

Závěrečné myšlenky

V tomto článku jsme prozkoumali několik metod, jak odstranit čísla z levé strany textových řetězců v Excelu. Každá technika má své výhody a omezení.

Žádná metoda není ideální pro každou situaci, ale určitě existuje přístup, který nejlépe vyhovuje vašim potřebám. S praxí se naučíte používat tyto techniky, abyste zvládli jakýkoli problém formátování dat, který se vám dostane do cesty.

Takže tady to máte, výhody a nevýhody odstranění nechtěných číslic zleva v Excelu. Zpočátku to může vypadat trochu složitě, ale se správnými technikami to zvládnete za chvíli! Šťastné skládání čísel!