Hoe sorteert u getallen met een tekst- of lettervoorvoegsel of -achtervoegsel in Excel?

Met deze methode introduceert u een door de gebruiker gedefinieerde functie (VBA) om alleen de getallen uit waarden met een voor- of achtervoegsel te extraheren, zodat u ze eenvoudig kunt sorteren.

1. Druk tegelijkertijd op Alt + F11 om het venster Microsoft Visual Basic for Applications te openen.

2. Klik op Invoegen > Module en plak vervolgens de onderstaande VBA-code in het nieuw geopende modulevenster.

Door de gebruiker gedefinieerde functie: Alleen getallen extraheren uit tekstreeksen

Function num(rng As Range) As String
Dim n As Integer
For n = 1 To Len(rng)
If Mid(rng, n, 1) Like "[0-9]" Then
num = num & Mid(rng, n, 1)
End If
Next n
End Function

3. Sla de code op en sluit het venster Microsoft Visual Basic for Applications.

4. Selecteer een lege cel naast de oorspronkelijke getallen met een voor- of achtervoegsel, bijvoorbeeld cel B2. Typ daar de formule =num(A2) (waarbij A2 het getal met voor- of achtervoegsel is) en sleep vervolgens de automatische invulgreep van deze cel naar het gewenste bereik.

5. Omdat de geëxtraheerde getallen als tekst zijn opgeslagen, moeten we ze converteren naar getallen. Kopieer alle geëxtraheerde getallen, klik met de rechtermuisknop op het gekopieerde bereik en selecteer Plakwaarden in het contextmenu. Zie screenshot:

Opmerking: In Excel 2007 kunt u de geëxtraheerde getallen die als tekst zijn opgeslagen kopiëren en vervolgens klikken op Start > Plakken > Plakwaarden.

6. Houd deze getallen geselecteerd, klik op de -knop naast de geselecteerde getallen en kies Converteren naar getal in de vervolgkeuzelijst. Zie screenshot:

7. Houd de nieuwe getallen geselecteerd en klik op Gegevens > Sorteer van kleinste naar grootste of Sorteer van grootste naar kleinste. Schakel vervolgens in het dialoogvenster Sorteerwaarschuwing de optie Selectie uitbreiden in en klik op de knop Sorteren. Zie screenshots:

8. Getallen met een letter als voor- of achtervoegsel worden nu gesorteerd zoals gewone getallen (zie onderstaande screenshot). Verwijder de geëxtraheerde getallen indien nodig.

Eigenlijk biedt Kutools voor Excel ook een vergelijkbare functie: EXTRACTNUMBERS, waarmee je getallen uit tekstreeksen kunt extraheren. Daarom kun je getallen met een lettervoor- of -achtervoegsel sorteren door eerst de getallen te extraheren.

1. Selecteer een lege cel naast de getallen met een lettervoor- of -achtervoegsel, bijvoorbeeld cel B2. Typ daar de formule =EXTRACTNUMBERS(A2;WAAR) in (A2 is de cel met het getal dat een lettervoor- of -achtervoegsel bevat) en sleep vervolgens de automatische invulgreep van deze cel naar het gewenste bereik. Zie screenshot:

2. Houd de geëxtraheerde getallen geselecteerd, klik op Gegevens > Sorteer Kleinste naar grootste of Sorteer Grootste naar kleinste; schakel in het dialoogvenster Sorteerwaarschuwing de optie Selectie uitbreiden in en klik vervolgens op de knop Sorteren. Zie onderstaande screenshots:

3. Getallen met een lettervoor- of -achtervoegsel worden nu gesorteerd zoals gewone getallen. U kunt de geëxtraheerde getallen desgewenst verwijderen.

Kutools voor Excel ondersteunt ook het splitsen van tekst en cijfers uit cellen met behulp van de functie Cellen splitsen. Zo kun je tekst en cijfers eenvoudig in afzonderlijke kolommen plaatsen, wat sorteren een stuk makkelijker maakt. Volg deze stappen:

1. Selecteer de getallen met een lettervoor- of -achtervoegsel en klik op Kutools > Tekst > Cellen splitsen. Zie onderstaande screenshot:

2. Schakel in het dialoogvenster Cellen splitsen dat wordt geopend de optie Splitsen naar kolommen in bij Type, selecteer Splitsen op tekst en nummer bij Splitsen op en klik op de knop OK. Zie bovenstaande screenshot:

3. Het tweede dialoogvenster Cellen splitsen verschijnt nu. Selecteer de cel naast de oorspronkelijke getallen met een lettervoor- of -achtervoegsel, bijvoorbeeld cel B2, en klik op de knop OK.
 

De Splitsen op tekst en nummer zijn nu gesplitst en naast de oorspronkelijke getallen met een voor- of achtervoegsel geplaatst. Zie onderstaande screenshot.

4. Selecteer de gesplitste getallen en klik op Gegevens > Sorteer Kleinste naar grootste of Sorteer Grootste naar kleinste. Schakel vervolgens de optie Selectie uitbreiden in het dialoogvenster Sorteerwaarschuwing in en klik op de knop Sorteren. Zie onderstaande screenshots:

5. Getallen met een lettervoor- of -achtervoegsel worden nu gesorteerd zoals gewone getallen. U kunt de optie ‘Splitsen op tekst en nummer’ desgewenst verwijderen.

Deze methode introduceert het hulpprogramma Kutools voor Excel Specifieke tekens verwijderen om alle niet-numerieke tekens (inclusief letters) te verwijderen en alleen getallen in de cellen te laten staan. Vervolgens kunt u getallen met een tekst-/lettervoor- of -achtervoegsel eenvoudig sorteren.

1. Kopieer alle getallen met een lettervoor- of -achtervoegsel en plak ze naast de oorspronkelijke getallen die ook een lettervoor- of -achtervoegsel hebben.

2. Houd de geplakte getallen geselecteerd, klik op Kutools > Tekst > Specifieke tekens verwijderen, schakel vervolgens alleen de optie Niet-numeriek in het dialoogvenster Specifieke tekens verwijderen dat verschijnt, en klik op de knop OK. Zie onderstaande screenshot:

3. Nu staan er alleen getallen in de nieuwe kolom. Houd deze getallen geselecteerd en klik op Gegevens > Sorteer van klein naar groot of Sorteer van groot naar klein. Schakel vervolgens in het dialoogvenster Sorteerwaarschuwing de optie Selectie uitbreiden in en klik op de knop SorterenZie de onderstaande screenshots:

4. Verwijder desgewenst de getallen.

Sorteer Volledige naam op Achternaam in Excel

Verjaardagen (datums) snel op maand sorteren in Excel

Cellen sorteren op laatste teken of cijfer in Excel

Gegevens sorteren op de meest voorkomende waarde in Excel