Phil hat eine Reihe von Zellen, die Text enthalten. Ein Teil des Textes ist zu lang, daher muss er ihn in mehrere Zellen aufteilen. Wenn eine Zelle beispielsweise „den schnellen braunen Fuchs“ enthält und er den Text so aufteilen muss, dass kein Teil länger als 12 Zeichen ist, kann Phil dies problemlos tun. Er möchte jedoch sicherstellen, dass die Aufteilung an der Stelle vor dem 12. Zeichen und nicht am exakten 12. Zeichen erfolgt. Phil weiß nicht, wie er das machen soll.

Sie könnten denken, Sie könnten das Text in Spalten-Werkzeug in Excel verwenden, aber es ist nicht gut für den Job geeignet. Wenn Sie das Tool so einstellen, dass Text basierend auf Trennzeichen wie einem Leerzeichen geteilt wird, erhalten Sie in jeder Zelle ein einzelnes Wort. Wenn Sie das Werkzeug so einstellen, dass der Text als „feste Breite“ geteilt wird, werden keine Wörter an Leerzeichen geteilt. Es wird lediglich sichergestellt, dass jeder Block die von Ihnen angegebene Größe hat.

Sie könnten eine Formel verwenden, um die gewünschten Ergebnisse zu erzielen, aber dies würde zu Formeln führen, die erstaunlich lang sind. Befindet sich der zu lange Text beispielsweise in Zelle A3, kann die folgende Formel verwendet werden, um den ersten Teil dieses Textes an der Stelle vor dem 12. Zeichen aufzuteilen:

=IF(LEN($A3)>12,IF(ISERROR(FIND(" ",MID($A3,12,1)

&MID($A3,11,1)&MID($A3,10,1)&MID($A3,9,1)&MID($A3,8,1)

&MID($A3,7,1)&MID($A3,6,1)&MID($A3,5,1)&MID($A3,4,1)

&MID($A3,3,1)&MID($A3,2,1))),MID($A3,1,1),MID($A3,1, 13-FIND(" ",MID($A3,12,1)&MID($A3,11,1)&MID($A3,10,1)

&MID($A3,9,1)&MID($A3,8,1)&MID($A3,7,1)&MID($A3,6,1)

&MID($A3,5,1)&MID($A3,4,1)&MID($A3,3,1)&MID($A3,2,1))))

,RIGHT($A3,LEN($A3)))

Denken Sie daran – dies ist alles eine einzige Formel, nur um den ersten Teil zu erhalten. Die Formeln für den zweiten, dritten, vierten und späteren Block sind noch länger. Die Verwendung einer Formel ist möglicherweise nicht der beste Ansatz.

Dadurch wird ein Makro verwendet. Ein Makro kann die Textzeichenfolge untersuchen und sie leicht in Stücke der gewünschten Länge zerlegen. Betrachten Sie die folgende benutzerdefinierte Funktion:

Function SplitMe(sSentence As String, iPos As Integer, Optional iLen = 12)

Dim sSegments() As String     Dim iSegments As Integer     Dim sRest As String     Dim sTemp As String     Dim iSpace As Integer     Dim J As Integer

iSegments = 0     sRest = sSentence     sTemp = Left(sRest, iLen + 1)

Do Until Len(sTemp) <= iLen         iSpace = 0         For J = Len(sTemp) To 1 Step -1             If Mid(sTemp, J, 1) = " " And iSpace = 0 Then iSpace = J         Next J         If iSpace > 0 Then             sTemp = Left(sRest, iSpace - 1)

sRest = Mid(sRest, iSpace + 1)

Else             sRest = Mid(sRest, Len(sTemp) + 1)

End If         iSegments = iSegments + 1         ReDim Preserve sSegments(1 To iSegments)

sSegments(iSegments) = sTemp         sTemp = Left(sRest, iLen + 1)

Loop     iSegments = iSegments + 1     ReDim Preserve sSegments(1 To iSegments)

sSegments(iSegments) = sTemp     If iPos <= iSegments Then         SplitMe = sSegments(iPos)

Else         SplitMe = ""

End If End Function

Die Funktion akzeptiert entweder zwei oder drei Parameter. Der erste Parameter ist die zu teilende Zeichenfolge, der zweite ist der gewünschte Block aus der Zeichenfolge und der dritte (und optionale) Parameter ist die gewünschte Länge jedes Blocks. Wenn Sie den dritten Parameter weglassen, geht die Funktion davon aus, dass jeder Block maximal 12 Zeichen enthalten soll. Angenommen, der Text befindet sich in Zelle A5, wird im Folgenden der zweite Block aus dem Text zurückgegeben, in dem jeder Block bis zu 12 Zeichen lang ist:

=SplitMe(A5,2)

Die Funktion liefert gute Ergebnisse, vorausgesetzt, jedes Wort in der Textzeichenfolge ist nicht länger als die angegebene Ziellänge für jeden Block. Wenn dies der Fall ist, erhalten Sie einige seltsame Ergebnisse, einschließlich einiger Abschnitte, die keine vollständigen Wörter enthalten.

_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 (3899) gilt für Microsoft Excel 97, 2000, 2002 und 2003.