Dit keer een korte.

Aantal waarden tellen die in één cel staan en gescheiden zijn door komma.
1,2,56,89,34,90,23,14,67

=LENGTE(A1)-LENGTE(SUBSTITUEREN(A1;",";""))+1

Antwoord = 9

Soms staan je gegevens op een ongelukkige manier gerangschikt in slechts één kolom. Dus vertikaal. Er zit wél regelmaat in. Bijvoorbeeld telkens 6 rijen gegevens die bij elkaar horen. Je kunt die gegevens eenvoudig verdelen zodat ze in rijen (horizontaal) komen te staan.

Zie afbeeldingen ter illustratie.
VOOR:

NA:

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
8. Formule invoegen in bijvoorbeeld [C1] =SplitText($A1; COLUMN()-2) en naar rechts doorvoeren.

Function SplitText(ByVal Cell As Range, ByVal LineNumber As Integer) As String
    Dim Lines As Variant
    Lines = Split(Cell.Value, Chr(10))
    If LineNumber - 1 <= UBound(Lines) Then
        SplitText = Lines(LineNumber - 1)
    Else
        SplitText = ""
    End If
End Function

Formule invoegen in bijvoorbeeld [C1] =SplitText($A1; COLUMN()-2)
en naar rechts doorvoeren

Hoe haal je het numerieke gedeelte op uit een alfanumerieke tekenreeks?
Bijvoorbeeld: als cel A3 de tekenreeks esd K+ WS4600 bevat, moet de waarde 4600 worden geretourneerd in cel B3 of in cel C3. Hiervoor hebben we 2 functies: strCountNumbers (in B3) en GetNumbers (in C3)

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
8. Formule invoegen in juiste cel.

Bijvoorbeeld in B3: =strCountNumbers(A3) of =GetNumbers(A3)

Function strCountNumbers(strText As String) As String
Dim intLengte As Integer, strChar As String, X As Integer, strNumber As String
    intLengte = Len(strText)
    For X = 1 To intLengte
        strChar = Mid(strText, X, 1)
        If strChar Like "[0-9]" Then
            strNumber = strNumber & strChar
        End If
    Next
    strCountNumbers = strNumber
End Function

Function GetNumbers(ByVal strText As String) As String
  Dim X As Long
  For X = 1 To Len(strText)
    If Mid(strText, X, 1) Like "[!0-9]" Then Mid(strText, X) = " "
  Next
  GetNumbers = Replace(strText, " ", "")
End Function

Getallen uit het BEGIN van een tekenreeks halen.

In [B3] =LEFT(A3; match(TRUE; MID
(A3 & "x"; {1\2\3\4\5\6\7\8\9\10\11\12}; 1) > "9"; 0) - 1)

Nog een bruikbare formule:
Formule in B3 invoeren

In [B3] =LEFT(A3;MATCH(TRUE;ISERROR(1*LEFT(SUBSTITUTE(IF(ISNUMBER
(-MID(A3;FIND(".";A3&".")+1;1));A3;SUBSTITUTE(A3;".";"X"))
;" ";"X")&"X";ROW(INDEX(A:A;1):INDEX(A:A;99))));0)-1)

Voornaam, achternaam, alias, tussenvoegsel en/of komma staan niet in goede volgorde. Dat moet toch te herstellen zijn.

Formule in B2 en eventueel naar beneden kopiëren.

=MID(REPLACE(A2;FIND(" (";A2&" (");0;" "&LEFT
(A2;FIND(",";A2)-1));FIND(",";A2)+2;99)

In [B13] =INDEX($B$5:$O$10;3;4)
In [B14] =INDEX($B$5:$O$10;6;7)
In [B15] =INDEX($B$5:$O$10;1;1)
In [B16] =INDEX($B$5:$O$10;2;10)

Data staat in het bereik A1:B11. In kolom A staat het ID en in kolom B Naam. Dat willen we van verticaal naar horizontaal verplaatsen oftewel transponeren

In [A14] =INDEX($A$1:$B$11;COLUMNS($A$1:A1);ROWS($A$1:$A1))
In [A16] =INDEX($A$1:$B$11;COLUMNS($A$1:A2);ROWS($A$1:$A2))

In een jaaroverzicht staan in de bovenste rij de namen van de maand. Daaronder bijvoorbeeld wat verkoopcijfers. Staan er dubbele waarden in dat bereik?

Formule in N2 en naar beneden kopiëren.

Formule invoeren met Ctrl+Shift+Enter. Niet alleen met Enter. Indien je dit correct doet, plaatst Excel accolades om de formule { }
Let op ! ! ! Plaats die accolades niet zelf. Formule in N2 en naar beneden kopiëren.

=IFERROR(SMALL(IF($A$2:$L$8<>"";IF(COUNTIF($A$2:$L$8;$A$2:$L$8)>1;$A$2:$L$8));SUM(COUNTIF($A$2:$L$8;N$1:N1))+1);"")

=LARGE(array,k)

De functie LARGE gebruikt een array met waarden in E2:E25. In waarde k geef je aan welke waarde je wil hebben, bijvoorbeeld de 5e waarde. Wij willen de 1e, 2e, 3e, 4e en 5e grootste waarden uit het bereik E2:E25.

Resultaat: {211,22; 193,37; 176,48; 79,25; 79,25}
oftewel:
€ 211,22
€ 193,37
€ 176,48
€ 79,25
€ 79,25

Formule in [G8] =SUM(LARGE($E$2:$E$25; {1;2;3;4;5}))

Het somt de vorige resulterende waarden op.
Resultaat: € 739,57

Schrijf celinhoud naar bestand C:\temp\textfile.html

Stel, je hebt in cel A1 de volgende tekst staan en je wilt die tekst naar een bestand schrijven.
“Lorem ipsum dolor sit amet, consectetur adipisicing elit,[ . . . ] sunt in culpa qui officia deserunt mollit anim id est laborum.”

Function Schrijf_Naar_Bestand(strCelInhoud As String) As Boolean
    Const LogFileName As String = "C:\temp\textfile.html"
    Dim FileNum As Integer
    
    'Volgende bestandsnummer
    FileNum = FreeFile
    
    'Maakt bestand aan indien niet aanwezig
    Open LogFileName For Append As #FileNum
    
    'Schrijft informatie weg aan het einde van het bestand
    Print #FileNum, strCelInhoud
    
    'Sluit het bestand
    Close #FileNum
    
    'Gelukt
    Schrijf_Naar_Bestand = True
End Function

1. Kopieer de bovenstaande 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
8. Plaats in een andere cel de volgende functie: =Schrijf_Naar_Bestand(A1)

Items staan in één cel namelijk A1. En er is sprake van tekst terugloop door voor elk nieuw item eerst Alt+ Enter te geven. Hierdoor komt elk item op een nieuwe regel. We willen die items echter splitsen en elk item in een eigen cel plaatsen zodat het overzichtelijker wordt. Zie screenshot:

Dit kan op 4 manieren:
– Met een simpele formule die je in B1 invoert en vervolgens naar rechts sleept (2x)
– Met VBA (2x)

In[B1] =TRIM(MID(SUBSTITUTE($A1; CHAR(10); REPT(" "; 100)); (COLUMN(A1)-1)*100+1; 100))
en naar rechts slepen.

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 SplitText(ByVal Cell As Range, ByVal LineNumber As Integer) As String
    Dim Lines As Variant
    Lines = Split(Cell.Value, Chr(10))
    If LineNumber - 1 <= UBound(Lines) Then
        SplitText = Lines(LineNumber - 1)
    Else
        SplitText = ""
    End If
End Function

Aangezien dit een functie is moet je nog één ding doen namelijk in B1 de volgende formule zetten:

In[B1] =SplitText($A1; COLUMN()-1)
en naar rechts slepen.

En de derde manier is ook VBA code:

Option Explicit
Sub Tekst_Terugloop_Van_Cel_Naar_Rij()
    Dim WsNieuw As Worksheet, rngBereik As Range
    Dim lngRij As Long, lngKolom As Long, lngVolgende As Long
    Dim lngTeller As Long, Data As Variant
    
    'Nieuw werkblad invoegen
    Set WsNieuw = Worksheets.Add
    
    'rngBereik is waar de gegevens staan
    With Worksheets("Sheet1")
        Set rngBereik = .Range("A1").CurrentRegion
        
        'Eerste rij met kolomtitels kopiëren
        rngBereik.Rows(1).Copy WsNieuw.Range("A1")
        lngVolgende = 2
        With rngBereik
            
            'Alle rijen doorlopen
            For lngRij = 2 To .Rows.Count
                lngTeller = 0
                
                'Alle kolommen doorlopen
                For lngKolom = 1 To .Columns.Count
                    
                    'Gegevens in cel splitsen
                    Data = Split(.Cells(lngRij, lngKolom).Value, _
                    Chr(10))
                    
                    'Gespliste gegevens wegschrijven naar rijen
                    WsNieuw.Cells(lngVolgende, lngKolom).Resize _
                    (UBound(Data) + 1).Value = Application.Transpose(Data)
                    
                    lngTeller = WorksheetFunction.Max(lngTeller, _
                    UBound(Data) + 1)
                Next lngKolom
                lngVolgende = lngVolgende + lngTeller
            Next lngRij
        End With
    End With
    WsNieuw.Cells.EntireColumn.AutoFit
End Sub

En de vierde manier:

in [B1] =TRIM(MID(SUBSTITUTE($A1;CHAR(10); REPT(" ";99));COLUMNS($A:A)*99-98;99))
en naar rechts slepen.