Manik hat ein Arbeitsblatt, das in Spalte A Textwerte im Format „mikeDAVIS“ enthält, wobei der Vorname der Person in Kleinbuchstaben und der Nachname in Großbuchstaben angegeben ist. Er möchte die Namen je nach Text in zwei separate Spalten aufteilen.

Dies kann entweder mit einer Formel oder einem Makro erreicht werden. Unabhängig davon, welchen Ansatz Sie verwenden, müssen Sie herausfinden, wo der Text von Klein- zu Großbuchstaben wechselt. Dies kann nur durch Untersuchen jedes Zeichens in der Zeichenfolge erfolgen. Wenn Sie also einen formelhaften Ansatz verwenden möchten, müssen Sie eine Array-Formel verwenden. Die folgende Array-Formel gibt den Nachnamen von allem zurück, was sich in Zelle A1 befindet:

=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$255),1))>=65)

* (CODE(MID(A1,ROW($2:$255),1))<90),)+1,255)

Denken Sie daran, da dies eine Array-Formel ist, sollten Sie sie durch Drücken von Strg + Umschalt + Eingabetaste eingeben. Es gibt alles in der Zelle zurück, beginnend mit dem ersten gefundenen Großbuchstaben. In „mikeDAVIS“ würde es also „DAVIS“ und in „mikeDavis“ „Davis“ zurückgeben. Angenommen, Sie verwenden die Array-Formel in Zelle B1, können Sie den Vornamen wie folgt ermitteln:

=SUBSTITUTE(A1,B1,"")

Dies ist eine reguläre Formel, keine Array-Formel.

Es gibt viele ähnliche Array-Formeln, die fast dieselbe Aufgabe erfüllen können. Diese Array-Formel gibt beispielsweise den Vornamen (alle Zeichen bis zum ersten Großbuchstaben) von allem zurück, was sich in Zelle A1 befindet:

=LEFT(A1,MAX((CODE(MID(A$1,ROW(INDIRECT("1:"& LEN(A1))),1))>96)*ROW(INDIRECT("1:"&LEN(A1)))))

Sie können dann dieselbe reguläre Formel (die die SUBSTITUTE-Funktion verwendet) verwenden, um den Nachnamen abzuleiten.

Wenn Sie einen Makroansatz verwenden möchten, um die Namen zu finden, müssen Sie lediglich eine Formel erstellen, die die Position des ersten Großbuchstabens im Text zurückgibt. Der folgende Code gibt diesen „Änderungspunkt“ im Text zurück:

Function GetFirstUpper(MyCell As Range) As Integer     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstUpper = 99     Else         GetFirstUpper = i     End If End Function

Um die Funktion zu verwenden, nehmen wir an, dass sich der Name in Zelle A1 befindet. Sie können den Vor- und Nachnamen mithilfe dieser Formeln in Ihrem Arbeitsblatt finden:

=LEFT(A1,GetFirstUpper(A1)-1)

=MID(A1,GetFirstUpper(A1),LEN(TRIM(A1))-GetFirstUpper(A1)+1)

Wenn Sie möchten, dass Ihr Makro die tatsächlichen Namen zurückgibt, können Sie Folgendes verwenden, um alles vor dem ersten Großbuchstaben zurückzugeben:

Function GetFirstName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstName = sCellValue     Else         GetFirstName = Left(sCellValue, i - 1)

End If End Function

Um das Makro zu verwenden, müssen Sie lediglich Folgendes in einer Arbeitsblattzelle verwenden. (Dies setzt voraus, dass sich die auszuwertende Textzeichenfolge in Zelle A1 befindet.)

=GetFirstName(A1)

Mit einer geringfügigen Änderung des Makros können Sie auf ähnliche Weise den Nachnamen abrufen, bei dem davon ausgegangen wird, dass alles mit dem ersten Großbuchstaben beginnt.

Function GetLastName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetLastName = sCellValue     Else         GetLastName = Mid(sCellValue, i)

End If End Function

Wenn Sie möchten, können Sie die Makros zu einer einzigen Funktion kombinieren, die je nach Angabe entweder den Vor- oder den Nachnamen zurückgibt:

Function GetName(MyCell As Range, sWanted As String) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetName = sCellValue     Else         If LCase(sWanted) = "first" Then             GetName = Left(sCellValue, i - 1)

Else             GetName = Mid(sCellValue, i)

End If     End If End Function

Um diese kombinierte Funktion nutzen zu können, müssen Sie lediglich den gewünschten Namen angeben:

=GetName(A1, "First")

Das auf diese Weise als Parameter übergebene Wort „First“ gibt den Vornamen zurück (alles vor dem ersten Großbuchstaben). Jede andere Zeichenfolge, die als zweiter Parameter übergeben wird (z. B. „Last“ oder „xxx“ oder „Rest“ oder sogar „“), führt dazu, dass der Nachname zurückgegeben wird.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (9091) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Zellen nach Fall teilen.