Voorbeeld met daar onder 3 formules die mogelijk zijn om een postcode te valideren. De drie rode postcodes hebben een verboden lettercombinatie. In de Nederlandse postcodes zijn de lettercombinaties SA, SD en SS verboden, omdat deze historisch gezien verband houden met de Tweede Wereldoorlog

In [B1] =AND(LEN(A1)=7;ISNUMBER(VALUE(LEFT(A1;4)));MID(A1;5;1)=" ";CODE(UPPER(MID(A1;6;1)))>=65;CODE(UPPER(MID(A1;6;1)))<=90;CODE(UPPER(MID(A1;7;1)))>=65;CODE(UPPER(MID(A1;7;1)))<=90;NOT(OR(RIGHT(A1;2)="SS";RIGHT(A1;2)="SA";RIGHT(A1;2)="SD")))

In [C1] =AND(LEN(A1)=7;ISNUMBER(VALUE(LEFT(A1;4)));MID(A1;5;1)=" ";UPPER(RIGHT(A1;2))<>"SS";UPPER(RIGHT(A1;2))<>"SA";UPPER(RIGHT(A1;2))<>"SD";SUMPRODUCT(--(ABS(CODE(MID(UPPER(RIGHT(A1;2));{1;2};1))-77,5)<13))=2)

In [D1] =AND(LEN(A1)=7;ISNUMBER(VALUE(LEFT(A1;4)));MID(A1;5;1)=" ";AND(CODE(UPPER(MID(A1;6;1)))>=65; CODE(UPPER(MID(A1;6;1)))<=90);AND(CODE(UPPER(MID(A1;7;1)))>=65; CODE(UPPER(MID(A1;7;1)))<=90);NOT(OR(UPPER(RIGHT(A1;2))="SS";UPPER(RIGHT(A1;2))="SA";UPPER(RIGHT(A1;2))="SD")))

Onderstaande code bevat 2 code blokken om te controleren of een URL een verwijzing is naar een afbeelding: “IsImageURL” om de status van de URL te verkrijgen c.q. of het om een afbeelding gaat en “CheckURLs” om elke URL in bereik $A$1:$A$15 te doorlopen. Te beginnen bij cel “$A$1”. Nieuwe module in je werkboek opnemen middels Alt+F11 en kiezen voor Invoegen | Module en dan de code plakken.

Code “CheckURLs” uitvoeren. Indien het om een ongeldige verwijzing gaat (dus geen afbeelding), komt er een foutbeschrijving in kolom “B” te staan.

Deze methode is gebaseerd op bestandsextensies en dat kan een beperking zijn. De “IsImageURL” functie kijkt of het einde van de URL eindigt op een gebruikelijke afbeelding extensie zoals (.jpg, .jpeg, .png, .gif, .bmp, .webp).

Function IsImageURL(URL As String) As Boolean
    ' List of common image file extensions
    Dim imageExtensions As Variant
    Dim extension As String
    Dim i As Integer
    
    ' Define common image extensions
    imageExtensions = Array(".jpg", ".jpeg", ".png", ".gif", ".bmp", ".webp", ".svg")
    
    ' Get the file extension from the URL
    extension = LCase(Right(URL, 5)) ' Check last 5 characters to cover .jpeg, .webp, etc.
    
    ' Check if the URL ends with an image extension
    For i = LBound(imageExtensions) To UBound(imageExtensions)
        If InStr(extension, imageExtensions(i)) > 0 Then
            IsImageURL = True
            Exit Function
        End If
    Next i
    
    IsImageURL = False
End Function

Sub CheckURLs()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    ' Set reference to Sheet1
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A1:A15")
    
    ' Loop through each cell in the range
    For Each cell In rng
        If Not IsEmpty(cell) Then
            ' Write TRUE/FALSE in the adjacent column (e.g., column B)
            cell.Offset(0, 1).Value = IsImageURL(cell.Value)
        End If
    Next cell
End Sub

Onlangs kreeg ik een werkblad toegestuurd met een vreemd datumformaat:
Okt 14th 2002 03:50 PM
Het werd door Excel als tekst gezien.
Om het te converteren naar een geldig datumformaat gebruikte ik deze formule.
De =ALS constructie is nodig om te bepalen of de dag uit 1 of 2 cijfers bestaat. Bijvoorbeeld 4th of 14th.
Let op ! ! ! Zet de celeigenschap op General.

Soms wil je de celadressen (bijvoorbeeld $D$2) weten waarin de naam van een item of tekenreeks voorkomt. Zie afbeelding voor de opzet.

Kolom A: City
Kolom B: Country
Kolom C: Address

Nu wil je weten in welke celadressen het land Argentina voorkomt.

In F2 komt de formule:

=IFERROR(CELL("address";INDEX($B$2:$B$94;SMALL(IF(ISNUMBER(FIND(" "&F$1&" ";" "&$B$2:$B$94&" "));ROW($B$2:$B$94)-ROW($B$2)+1);ROWS($F$2:F2))));"")

Let op ! ! ! Dit is een Matrixformule en MOET ingevoerd worden met Ctrl+Shift+Enter dus NIET met ENTER.

Vervolgens formule naar beneden kopiëren bijvoorbeeld van F3 tot F10.

Om het gemakkelijk te maken kun je in kolom H een lijst opnemen met alle landennamen die voorkomen. Die lijst is met behulp van gegevensvalidatie gekoppeld aan cel F1. Onderstaand venster kun je bereiken d.m.v. 

Gegevens | Gegevensvalidatie | Instellingen

Bij “Toestaan” kies je voor “Lijst” en bij “Bron” geef je het bereik op dus: $H$2:$H$22
Klik OK.

Door het toepassen van voorwaardelijke opmaak kun je snel verschillen opmerken in een bereik met waarden. Wat moet je doen?

Selecteer de gegevens waarop je voorwaardelijke opmaak wil toepassen.

Vervolgens klik je op:
Voorwaardelijke opmaak | Markeerregels voor cellen | Tekst met

In het onderstaande vak kun je de naam van een land invoeren maar dat is niet handig want dat beperkt de boel tot dat land. Beter is om naar een cel te verwijzen in dit geval G2. Telkens als je nu de waarde in G2 verandert, verandert de kleurmarkering naar het land dat je invoert.
Let op ! ! ! In het rechtervak kun je nog een mooie opmaak kiezen. Experimenteren maar.

In G3 kun je volgende formule plaatsen:

=IFERROR(CELL("address";INDEX($B$2:$B$94;SMALL(IF(ISNUMBER(FIND(" "&G$3&" ";" "&$B$2:$B$94&" "));ROW($B$2:$B$94)-ROW($B$2)+1);ROWS($G$4:G4))));"")

Let op ! ! ! Dit is een Matrixformule en MOET ingevoerd worden met Ctrl+Shift+Enter dus NIET met ENTER.

Vervolgens formule naar beneden kopiëren bijvoorbeeld van G4 tot G10.

Het probleem is dat je veel dubbele waarden in kolom A (Soort) hebt.

Met de functie VERT.ZOEKEN kun je daarom niet goed uit de voeten. Echter, je kunt kolom A en Kolom B samenvoegen in je formule d.m.v. het & teken. Met de functies INDEX en VERGELIJKEN kun je toch de juiste waarden opzoeken.

In cel C3 de formule:

=IFERROR(INDEX($G$4:$G$20;MATCH(A4&B4;$E$4:$E$20&$F$4:$F$20;0));"")

Let op ! ! ! Dit is een Matrixformule en MOET ingevoerd worden met Ctrl+Shift+Enter dus NIET met ENTER.

P.s. Je kunt de kolommen natuurlijk ook verwisselen. B wordt A en A wordt B maar daar gaat het hier niet om.

Stel dat je een kolom H hebt met prijzen van artikelen en je wilt de prijzen omschrijven (labelen) (Kolom G) op basis van de volgende voorwaarden:

Een manier is om een ​​label (Kolom G) te kiezen dat overeenkomt met de conditie PRIJS (Kolom H). Om flexibel te zijn werken we met celverwijzingen en niet met ‘hard coded’ data

=CHOOSE((D8>=$H$2) + (D8>=$H$3) + (D8>=$H$4) + (D8>=$H$5) + (D8>=$H$6) + (D8>=$H$7) + (D8>=$H$8) + (D8>=$H$9)  + (D8>=$H$10); $G$2;$G$3;$G$4;$G$5;$G$6;$G$7;$G$8;$G$9;$G$10)

Dit resulteert in:

=CHOOSE((TRUE) + (TRUE) + (TRUE) + (TRUE) + (TRUE) + (TRUE) + (TRUE) + (TRUE)  + (TRUE); "Koopje";"Goedkoop";"Prima prijs";"Normaal";"Acceptabel";"Prijzig";"Duur";"Peperduur";"Absurd")

Hoe deze formule werkt:

In het argument index_num evalueer je elke voorwaarde en retourneert TRUE als aan de voorwaarde is voldaan, anders FALSE. De waarde in cel E8 voldoet bijvoorbeeld aan alle 9 voorwaarden, want €3360,50 is groter dan alle andere waarden dus krijgen we dit tussenresultaat:

=CHOOSE((TRUE) + (TRUE) + (TRUE) + (TRUE) + (TRUE) + (TRUE) + (TRUE) + (TRUE ) + (TRUE ); $G$2;$G$3;$G$4;$G$5;$G$6;$G$7;$G$8;$G$9;$G$10)

Aangezien in de meeste Excel-formules TRUE gelijk is aan 1 en FALSE gelijk is aan 0, ondergaat onze formule de volgende transformatie:

=CHOOSE((1) + (1) + (1) + (1) + (1) + (1) + (1) + (1) + (1); $G$2;$G$3;$G$4;$G$5;$G$6;$G$7;$G$8;$G$9;$G$10)

Nadat de optelling is uitgevoerd, hebben we:

=CHOOSE( 9 ; "Koopje";"Goedkoop";"Prima prijs";"Normaal";"Acceptabel";"Prijzig";"Duur";"Peperduur";"Absurd")

Het resultaat van de optelling is 9 en het resultaat wordt de 9e ^waarde in de lijst geretourneerd, namelijk “Absurd”.

Opzet:

Stel je hebt onderstaande tabel en je wilt de naam van de Employee opzoeken die uit Switzerland komt en je wilt dat met VERT.ZOEKEN doen. Probleem, VERT.ZOEKEN neemt altijd een waarde in de meest linkse kolom om vervolgens in de kolom die meer naar rechts staat te kijken. Eigenlijk zou kolom C dus de meest linkse moeten zijn. en kolom A meer rechts moeten staan ten opzichte van kolom C. Maar we willen de indeling van de tabel niet gaan veranderen door kolommen te gaan verschuiven.

Met de functie CHOOSE kun je kolommen als het ware virtueel verschuiven, We willen kolom C naar A verschuiven en Kolom A naar C verschuiven. Kolom B kan blijven staan. Met de volgende formule kan dat:

=VLOOKUP(E2;CHOOSE({3\2\1};A2:A9;B2:B9;C2:C9);3;0)

Uitleg:
– Eerst maak je in E2 een drop down met de landen die in kolom C staan. Ga Naar:
Data | Data Validation | Data Validation | en kies voor Allow | List en bij Source zet je $C$2:$C$9. Klik OK.

– In E2 kun je nu een land kiezen bijvoorbeeld Zwitserland. Bij CHOOSE zie je als eerste argument staan {3\2\1} Dat is een matrix en de cijfers worden gescheiden door de backslash \ Als tweede argument zie je 3 bereiken A2:A9;B2:B9;C2:C9. Wat CHOOSE nu doet is het eerste bereik wordt de 3e kolom, het tweede bereik de 2e kolom en het derde bereik de 1-ste kolom. Dat derde bereik wordt dus de meest linkse kolom. Je snapt het al je kunt nu wél de functie VERT.ZOEKEN gebruiken.
Met CHOOSE kun je als het ware kolommen verschuiven en er zijn veel meer mogelijkheden met die functie. 2 simpele voorbeelden:

CHOOSE(2,"red","blue","green") // returns "blue"
CHOOSE(3,"red","blue","green") // returns "green"

Dit keer in het engels.

Put this example in A2:
Smith John James 12346578
Lastname always come first, the number is always last, in between always one or multiple firstnames

We want to split Lastname, Firstname(s), Number. And the firstname(s) should be only Initials.

The goal is to get the final result, Number & Initials & Lastname in G2:
In [G2]:
12345678 JJ Smith

When your done, you can hide the columns, B, C, D, E, F if you want.

Formula in B2:

=LOOKUP(10^9;--RIGHT(A2;{1;2;3;4;5;6;7;8;9}))

Formula in C2:

=TRIM(LEFT(A2; LEN(A2)-LEN(B2)))

Formula in D2:

=LEFT(C2; FIND(" "; C2 & " ")-1)

Formula in E2:

=TRIM(SUBSTITUTE(C2; D2; ""))

Formula in F2:

=GetInitials(E2)

Formula in G2:

=B2 & ” ” & UPPER(F2) & ” ” & D2

1. Kopieer de onderstaande code
2. Open een nieuwe werkmap
3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
5. Druk op M om een standaard module in te voegen
6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel

Function GetInitials(rng As Range) As String
    Dim FullName As String
    Dim i As Integer
    Dim Initials As String
    
    FullName = rng.Value
    Initials = Left(FullName, 1)
    
    For i = 2 To Len(FullName)
        If Mid(FullName, i - 1, 1) = " " Then
            Initials = Initials & Mid(FullName, i, 1)
        End If
    Next i
    
    GetInitials = UCase(Initials)
End Function

Put the formule in F2:
=GetInitials(E2)

De onderstaande zogenaamde “User Defined function” oftewel een zelfgemaakte functie, splitst de gegevens die in 1 cel staan en meerdere regels omvat.

Opzet:
– Je gegevens staan in kolom A. In dit geval alleen in A1.
– Je krijgt dan als resultaat zoals je het op de afbeelding ziet.

1. Kopieer de onderstaande code
2. Open een nieuwe werkmap
3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
5. Druk op M om een standaard module in te voegen
6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel

Function Split_It_Up(rngBereik As Range, RijNummer)
    
    'Altijd herberekenen
    Application.Volatile
    
    a = InStr(1, rngBereik.Value, Chr(10), 1)
    b = InStr(a + 1, rngBereik.Value, Chr(10), 1)
    c = InStr(b + 1, rngBereik.Value, Chr(10), 1)
    d = InStr(c + 1, rngBereik.Value, Chr(10), 1)
    e = InStr(d + 1, rngBereik.Value, Chr(10), 1)
    If RijNummer = 1 Then Split_It_Up = Left(rngBereik.Value, a - 1)
    If RijNummer = 2 Then Split_It_Up = Mid(rngBereik.Value, a + 1, (b) - (a + 1))
    If RijNummer = 3 Then Split_It_Up = Mid(rngBereik.Value, b + 1, (c) - (b + 1))
    If RijNummer = 4 Then Split_It_Up = Mid(rngBereik.Value, c + 1, (d) - (c + 1))
    If RijNummer = 5 Then Split_It_Up = Mid(rngBereik.Value, d + 1, (e) - (d + 1))
    If RijNummer = 6 Then Split_It_Up = Right(rngBereik.Value, Len(rngBereik.Value) - e)
End Function

Zet de volgende formule in C1:
=Split_It_Up($A1;KOLOM(A1))
En naar rechts slepen (en eventueel naar beneden)