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)