Excel, alle Ver­sio­nen

In einem früheren Beitrag haben wir uns schon ein­mal mit dem The­ma auseinan­der geset­zt. Dort wurde aufgezeigt, wie Sie uner­wün­schte Leerze­ichen, so genan­nte „Geschützte Leerze­ichen” ent­fer­nen oder durch „nor­male” Leerze­ichen erset­zen kön­nen. Und das Ganze sog­ar vol­lkom­men ohne VBA.  Uns vielle­icht sind Sie zum Schluss gekom­men, dass diese beson­deren Zeichen in Excel keine Berech­ti­gung haben. Na ja … Stellen Sie sich fol­gende Text­datei vor:

Vorname Name Raum
Peter Lustig Raum 5
Lieschen Müller Raum 12a
Heinrich Pumpernickel Kantine

Wenn Sie diese Text­datei per copy/paste ein­fü­gen oder ein­le­sen, dann wird (natür­lich) alles in Spalte A einge­fügt. Mit der Funk­tion­al­ität Text in Spal­ten kön­nen Sie erre­ichen, dass die einzel­nen Ele­mente aufgeteilt wer­den. Hier ist klar, dass das Leerze­ichen (Leer­schlag) das tren­nende Ele­ment ist. Aber rasch wer­den Sie erken­nen, dass das nicht so wirk­lich opti­mal abläuft. Die Über­schrift hat drei Spal­ten, die bei­den ersten Daten­sätze sind aber nach dem Aufteilen vier Spal­ten groß, nur der dritte Daten­satz passt.

Das ist ja auch logisch, denn nach „Raum” kommt ja auch ein Leerze­ichen und das wird entsprechend auch als Tren­ner gew­ertet. Bei den weni­gen Zeilen ist das ja noch ganz gut per Hand reg­ulier­bar, aber die meis­ten importierten oder einge­fügten Dateien sind schon umfan­gre­ich­er. Wie lässt sich dieses Dilem­ma beseit­i­gen?

Eine Möglichkeit wäre, in ein­er Hil­f­ss­palte fol­gende Funk­tion einzufü­gen: 
=GLÄTTEN(C2 & ” ” & D2)
und das Ganze dann nach unten kopieren. Durch das GLÄTTEN() wird in Zeile 4 kein Leerze­ichen ange­hängt bzw. es wird wieder ent­fer­nt. Den kom­plet­ten Bere­ich dann kopieren und Als Wert anschließend in Spalte C ein­fü­gen, wobei beste­hende Ein­träge natür­lich über­schrieben wer­den. Die Spalte D kann, nein sollte dann gelöscht wer­den. So weit, so gut. Nun stellen Sie sich ein­mal fol­gen­des Szenario vor:

Vorname Name Raum Haus Durchwahl
Peter Lustig Raum 5 Haupthaus 248
Lieschen Müller Raum 12a Nebenhaus 2 166
Heinrich Pumpernickel Kantine Nebenhaus 1 123

Da wird die bish­erige Vorge­hensweise dann „eng”  😥 . Das artet in richtige Arbeit aus. 😉  Eine andere Lösung wäre schön. Und die ist dur­chaus mach­bar. Sie erset­zen unter bes­timmten Kri­te­rien das „nor­male” Leerze­ichen durch das geschützte. Im obi­gen Beispiel soll nach „Raum” statt des Leerze­ichens ein Geschütztes Leerze­ichen kom­men und somit nicht als Tren­ner wirken. Gle­ich­es gilt für das „Neben­haus”. Fol­gen­des Vorge­hen bietet sich an:

• Fügen Sie direkt nach dem Import oder Ein­fü­gen die entsprechen­den Dat­en, welche ja alle in (nor­maler­weise) Spalte A ste­hen, in eine beliebige andere Spalte diese Formel ein:
  =WECHSELN(A1; "Raum "; "Raum ")
  wobei Sie das erste "Raum " mit einem nor­malen Leerze­ichen eingeben, beim zweit­en "Raum" gehen Sie so vor:
  • Kein Leer­schritt nach dem Wort!
  • Drück­en Sie die Taste Alt und hal­ten Sie diese gedrückt.
  • Auf dem Zehnerblock, also nicht der „nor­malen” Tas­tatur drück­en Sie nacheinan­der 0160, danach lassen Sie Alt los. Bei manchen Note­book-Tas­taturen wer­den Sie die Fn-Taste zusät­zlich nutzen müssen, um den Num­mern­block zu imi­tieren.
• Kopieren Sie die Formel nach unten.
• Markieren Sie diese neuen Ergeb­nisse und kopieren Sie diese in die Zwis­chen­ablage.
• Fügen Sie diese kor­rigierten Dat­en in Spalte A Als Wert ein.
• Führen Sie den Vor­gang gle­icher­maßen für die Haus-Beze­ich­nung durch.

Wenn Sie jet­zt die Dat­en über die bekan­nte Funk­tion­al­ität Text in Spal­ten aufteilen, wird Ihnen den­noch eine kleine Ungereimtheit auf­fall­en:

Text in Spal­ten mit einem kleinen Hin­der­nis

In der Über­schrift ist (natür­lich) auch der "Raum " mit einem geschützten Leerze­ichen erset­zt wor­den.  Das kön­nen Sie entwed­er von Hand kor­rigieren (was ja nicht so aufwendig ist) oder Sie acht­en darauf, dass die Formel nicht in der Über­schrift­szeile angewen­det wird.

Aus­nahm­sweise hier ein klein­er Exkurs in die Welt des Makros, ein kurz­er VBA-Code. Wenn es näm­lich mehrere Werte zu erset­zen gibt, dann kann das ganz hil­fre­ich sein. Beim obi­gen Beispiel kön­nte der Code so ausse­hen:

Sub SplitText()
   Dim c As Range
   
   For Each c In Selection
      If InStr(c, "Raum ") > 1 Then
         c = WorksheetFunction.Substitute(c, "Raum ", "Raum" & Chr(160))
      End If
      If InStr(c, "Nebenhaus ") > 1 Then
         c = WorksheetFunction.Substitute(c, "Nebenhaus ", "Nebenhaus" & Chr(160))
      End If
   Next c
   'Und Text in Spalten (wie aufgezeichnet) durchführen
   Selection.TextToColumns Destination:=Range("A1"), _
    DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
    Comma:=False, Space:=True, Other:=False, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
    Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
End Sub

Die Kopfzeile muss in jedem Fall nach­bear­beit­et wer­den. Wenn sie mit markiert war, dann wird ab „Raum” ein „nor­males” Leerze­ichen einge­fügt wer­den und danach alles um 1 Spalte ver­schoben. Oder aber diese Zeile wird getren­nt per Hand aufgeteilt.