Aufteilen von Zellen nach Fall (Microsoft Excel)
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 für ein 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 Application.Volatile 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 Application.Volatile 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 Application.Volatile 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 Application.Volatile 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.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (9089) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier: