Nummers in Excel van links verwijderen: het is eenvoudig!

Er zijn momenten waarop u werkt met gegevens die niet zijn opgemaakt op de manier die u nodig heeft. U hebt bijvoorbeeld een combinatie van cijfers en tekst in een cel en u moet deze gegevens opschonen.

Er zijn verschillende technieken om getallen in Excel aan de linkerkant van de tekst te verwijderen. Sommige methoden werken wanneer de gegevens een consistent patroon hebben. VBA of complexere formules werken op minder gestructureerde gegevens.

Dit artikel demonstreert zeven methoden om nummers aan de linkerkant van tekstgegevens te verwijderen. Aan het einde heb je een uitgebreid begrip van de verschillende technieken en wanneer je ze moet gebruiken.

Laten we gaan!

Inhoudsopgave

• Flash Fill gebruiken om getallen in Excel van links te verwijderen
• Hoe de functie Tekst naar kolommen te gebruiken om getallen te verwijderen
• 5 tekstfuncties om getallen in Excel van links te verwijderen
  • 1. MID-functie
  • 2. Combineer met de LEN-functie
  • 3. De functies LINKS en VERVANGEN combineren
  • 4. De RECHTS- en LEN-functie gebruiken
  • 5. De functie VERVANGEN gebruiken
• Power Query gebruiken om nummers van links te verwijderen
• 3 geavanceerde technieken, ongeacht hoeveel tekens
  • 1. Combineer een hulpkolom met een formule om tekens links van cellen te verwijderen
  • 2. Maak een VBA-macro om nummers te verwijderen
  • 3. Creëer een door de gebruiker gedefinieerde functie
• Laatste gedachten

Flash Fill gebruiken om getallen in Excel van links te verwijderen

Flash Fill is een Excel-functie die automatisch patronen voor u kan invullen. Het is een snelle en gemakkelijke manier om getallen aan de linkerkant van cellen te verwijderen.

Deze methode werkt misschien niet altijd perfect, maar kan in eenvoudige gevallen nuttig zijn. Volg deze stappen om Flash Fill te gebruiken:

1. Typ in een lege kolom naast uw gegevens het gewenste resultaat zonder de cijfers (als bijvoorbeeld "1234apple" in cel A1 staat, typt u "apple" in cel B1).

2. Selecteer de cel met de nieuwe gegevens (B1 in ons voorbeeld) en klik op het tabblad Gegevens op het lint.

3. Klik op de knop Flash Fill in het gedeelte Gegevenstools.

4. Excel past het patroon automatisch toe om getallen aan de linkerkant van de aangrenzende cellen te verwijderen.

Dit is de locatie van de Flash Fill-knop op het tabblad Gegevens.

Als u liever snelkoppelingen gebruikt, drukt u op CTRL + E op Windows of CMD + E op een Mac.

Flash Fill is een handige functie, maar zoals met de meeste dingen in Excel, zijn er meerdere manieren om dezelfde taak uit te voeren.

In het volgende gedeelte bekijken we hoe u hetzelfde kunt doen met de functie "Tekst naar kolommen".

Hoe de functie Tekst naar kolommen te gebruiken om getallen te verwijderen

U kunt de functie "Tekst naar kolommen" in Excel gebruiken om getallen aan de linkerkant van een cel te scheiden. Bij deze methode wordt er echter van uitgegaan dat er een consistent patroon of scheidingsteken is tussen de getallen en de tekst in de cel.

Laten we werken met een set gegevens die een streepje gebruikt om cijfers van letters te scheiden, zoals deze:

• 1234-appel

• 4321-oranje

• 9876-peer

Dit is het voor-en-na dat we willen bereiken:

Hier is een stapsgewijze handleiding met deze voorbeeldgegevensset:

1. Selecteer kolom A.

2. Ga naar het tabblad Gegevens en klik op de wizard Tekst naar kolommen.

3. Kies de optie Gescheiden en klik op Volgende.

4. Schakel alle scheidingstekens uit (de tab kan standaard worden toegepast).

5. Vink het andere scheidingsteken aan en typ "-" in het invoervak.

6. Selecteer het juiste scheidingsteken (spatie, komma, enz.).

7. Klik op Volgende en Voltooien.

De gegevens worden nu gescheiden in twee kolommen. De verwijderde tekens staan ​​in de eerste kolom terwijl de rest van de tekst in de tweede kolom staat. Deze afbeelding laat zien hoe u het scheidingsteken instelt:

Laten we vervolgens eens kijken naar vijf tekstfuncties voor het verwijderen van getallen in Excel.

5 tekstfuncties om getallen in Excel van links te verwijderen

Als u weet uit hoeveel tekens de cijfers bestaan, kunt u een tekstfunctie gebruiken om ze te verwijderen.

Stel dat cel A1 de tekenreeks " 1234apple " bevat. De eerste vier tekens zijn cijfers die we willen verwijderen.

Er zijn verschillende functies die afzonderlijk of gecombineerd kunnen worden gebruikt voor het verwijderen van tekens die numerieke waarden zijn.

1. MID-functie

U kunt de MID-functie gebruiken om het deel van de tekenreeks te extraheren dat begint met het 5e teken.

De MID-functie extraheert een specifiek gedeelte van een tekenreeks, gegeven de beginpositie en het aantal tekens dat moet worden geëxtraheerd. De syntax voor de MID-functie is:

MID(tekst, begin_getal, aantal_tekens)

• tekst: De tekenreeks die de tekens bevat die u wilt extraheren.

• start_num: de positie van het eerste teken dat u wilt extraheren.

• num_chars: het aantal tekens dat moet worden geëxtraheerd uit de tekenreeks.

Volg deze stappen om de nummers aan de linkerkant te verwijderen:

1. selecteer cel B2 om de functie en resultaten te bevatten.

2. Typ de formule: MIDDEN(A1, 5, 7)

De bovenstaande formule verwijdert tekens uit het 5e slot in de string.

2. Combineer met de LEN-functie

Een nadeel van deze methode is dat uw tekenreeks niet altijd dezelfde lengte heeft.

U kunt de LEN-functie gebruiken om de lengte dynamisch te berekenen. Deze functie retourneert de lengte van een tekenreeks.

U kunt de formule wijzigen om de derde parameter als volgt te vervangen door de LEN-functie:

=MIDDEN(A1, 5; LENGTE(A1)-4)

U kunt de formule naar beneden kopiëren met behulp van de vulgreep. Deze afbeelding toont de bovenstaande formule in gebruik:

3. De functies LINKS en VERVANGEN combineren

De functie LINKS retourneert de meest linkse tekens uit een tekenreeks. LINKS(“1234appel”, 4) zou bijvoorbeeld de waarde “1234” teruggeven.

Dit is handig wanneer u de getallen wilt extraheren. Maar wat als u ze wilt verwijderen?

U kunt LINKS combineren met de functie VERVANGEN om het getal te vervangen door een lege tekenreeks. Dit is het formaat voor ons voorbeeld:

=VERVANGEN(A1, LINKS(A1, 4), "")

4. De RECHTS- en LEN-functie gebruiken

De functie RECHTS retourneert de meest rechtse tekens uit een tekenreeks. De syntaxis voor de functie RECHTS is:

RECHTS(tekst, [aantal_tekens])

• tekst: De tekenreeks die de tekens bevat die u wilt extraheren.

• num_chars: (Optioneel) Het aantal tekens dat aan de rechterkant van de tekenreeks moet worden geëxtraheerd. Indien weggelaten, is het standaard 1.

RECHTS(“1234appel”, 5) zou bijvoorbeeld de waarde “appel” teruggeven.

U kunt ook de RIGHT- en LEN-functies combineren om harde codering van het aantal alfatekens te voorkomen. Hierbij wordt ervan uitgegaan dat u het aantal numerieke tekens kent.

Het formaat voor ons voorbeeld is: =RIGHT(A1, LEN(A1)-4)

5. De functie VERVANGEN gebruiken

De functie VERVANGEN kan worden gebruikt om een ​​bepaald aantal tekens van links in een cel te vervangen. Dit is de syntaxis:

REPLACE(oude_tekst, begin_getal, aantal_tekens, nieuwe_tekst)

• old_text: de oorspronkelijke tekstreeks die u wilt wijzigen.

• start_num: de positie in de oude_tekst waar u wilt beginnen met het vervangen van tekens.

• num_chars: het aantal tekens in de oude_tekst dat u wilt vervangen.

• nieuwe_tekst: de tekenreeks waarmee u de opgegeven tekens wilt vervangen.

De volgende formule is voor onze voorbeeldgegevens:

=VERVANGEN(A1, 1, 4, "")

En dat is de laatste van onze vijf tekstfuncties voor het verwijderen van tekens van links in Excel.

In de meeste gevallen zouden de zeven methoden die we tot nu toe hebben besproken voldoende moeten zijn, maar als u meer kracht wilt, is Power Query het antwoord, en daar zullen we het in de volgende sectie over hebben!

Power Query gebruiken om nummers van links te verwijderen

Power Query is een krachtige tool voor gegevenstransformatie die beschikbaar is in Excel. De methode die in deze sectie wordt getoond, gaat ervan uit dat er een scheidingsteken is tussen de getallen en de tekst in de cel.

De eerste stap is om de doelgegevens om te zetten in een tabel:

1. Ga naar het tabblad Gegevens.

2. Selecteer het celbereik.

3. Kies "Uit tabel/bereik" in het gedeelte Gegevens ophalen op het lint.

Wanneer de Power Query-editor wordt geopend, volgt u deze stappen:

1. Selecteer de doelkolom(men).

2. Ga naar het tabblad "Transformeren".

3. Klik op "Uitpakken" in het lint en kies "Tekst na scheidingsteken".

4. Voer in het dialoogvenster "Tekst na scheidingsteken" een streepje (-) in.

5. Klik op "OK" om de details te accepteren.

De getransformeerde kolom heeft nu de nummers links van de tekst verwijderd.

Klik op "Sluiten en laden" om de getransformeerde gegevens terug in uw werkblad te laden.

Als u nog niet bekend bent met deze gegevenstool, bekijk dan onze zelfstudie over het gebruik van Power Query in Excel .

Als u dieper wilt ingaan op het gebruik van tabellen in Power Query, laat deze video enkele complexere voorbeelden zien:

3 geavanceerde technieken, ongeacht hoeveel tekens

Het belangrijkste nadeel van de methoden tot nu toe is dat de getallen in alle cellen hetzelfde patroon moeten volgen of een bepaald aantal karakters moeten hebben.

Wat als u tekens wilt verwijderen zonder een duidelijk patroon in de originele gegevens? De technieken in deze sectie zijn bedoeld voor gemiddelde Excel-gebruikers.

1. Combineer een hulpkolom met een formule om tekens links van cellen te verwijderen

U kunt functies combineren met hulpkolommen als u de totale lengte van de getallen niet weet. Deze methode vereist een proces in twee stappen:

• gebruik een formule om een ​​tijdelijke gewijzigde versie van de oorspronkelijke tekst te maken

• gebruik een andere formule om de getallen te verwijderen op basis van de gewijzigde tekst.

De eerste stap is om de volgende formule te gebruiken om alle getallen te vervangen door een specifiek teken (bijv. "|"):

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

De tweede stap is om de volgende formule te gebruiken in een andere helperkolom (bijvoorbeeld kolom C):

=TRIM(RECHTS(VERVANGEN(B1, "|", HERHALEN(" ", LENGTE(B1))), LENGTE(B1)))

Dit is de voor-en-na met de formules in kolom B en C:

Wanneer u dergelijke functies gaat combineren, kunt u problemen krijgen met het kiezen van de verkeerde opties in uw formules. Ons artikel over het vinden van kringverwijzingen in Excel helpt u bij het oplossen van een veelvoorkomende fout.

Misschien wilt u ook een spreadsheet met hulpkolommen delen. Het is belangrijk dat je collega's de formules in de tussenresultaten niet verwijderen of aanpassen. Ons artikel over manieren om kolommen in Excel te vergrendelen, helpt u bij het produceren van de functionaliteit.

2. Maak een VBA-macro om nummers te verwijderen

VBA is een veelzijdige scripttaal waarmee u taken kunt automatiseren en aangepaste oplossingen binnen Excel kunt maken.

U kunt bijvoorbeeld een macro schrijven die getallen aan de linkerkant van alle cellen in een specifieke kolom of bereik verwijdert.

Volg hiervoor deze stappen:

1. Druk op ALT + F11 om de VBA-editor te openen.

2. Klik op Invoegen > Module om een ​​nieuwe module te maken.

3. Kopieer en plak de volgende code om alleen getallen aan de linkerkant van kolom A te verwijderen:

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

De laatste stap is het uitvoeren van de macro. Klik op de groene driehoek of het menu-item "uitvoeren".

3. Creëer een door de gebruiker gedefinieerde functie

U kunt ook een door de gebruiker gedefinieerde functie maken met VBA die u in uw formules kunt gebruiken.

Volg hiervoor deze stappen:

1. Druk op ALT + F11 om de VBA-editor te openen.

2. Klik op Invoegen > Module om een ​​nieuwe module te maken.

3. Kopieer en plak de volgende code:

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

Ga nu terug naar uw Excel-blad en gebruik de openbare functie in een formule.

Deze formule werkt bijvoorbeeld door de A1-cel te accepteren:

=VerwijderNummersVanLinks(A1)

Laatste gedachten

In dit artikel hebben we meerdere methoden onderzocht om getallen aan de linkerkant van tekenreeksen in Excel te verwijderen. Elke techniek heeft zijn eigen voordelen en beperkingen.

Geen enkele methode is perfect voor elke situatie, maar er is zeker een aanpak die het beste aansluit bij uw behoeften. Door te oefenen, zult u bedreven raken in het toepassen van deze technieken om elke uitdaging op het gebied van gegevensopmaak aan te kunnen.

Dus daar heb je het, de ins en outs van het verwijderen van ongewenste cijfers van links in Excel. Het ziet er in het begin misschien een beetje lastig uit, maar met de juiste technieken ben je er binnen de kortste keren de baas over! Veel plezier met rekenen!