Frage von Nebelkrieger, 80

Ich will mehrere Zellen in Exel verketten die ich vorher ausgewählt habe und diese sollen dann ohne Zeilenumbruch eingefügt werden. Wie mach ich das am besten?

Expertenantwort
von Iamiam, Community-Experte für Excel, 29

Wenn Du die Auswahl nicht verlieren willst, dann Makro unten.

Wenn Du sie festschreiben willst, kannst Du einen Bereichsnamen dafür vergeben, zB HundertZellen, den Du (bei fortbestehender Markierung) in das Adressfeld links oben einfach reinschreibst. Wählst Du diesen Bereich, sind auch später wieder alle Zellen ausgewählt.

Makro:

Sub ZellinhalteVielfachAuswahlVerketten()
Dim c As Range, VTxt As String
'Range("hundertZellen").select 'ü´flüss nach obiger Anweisung
VTxt = ""
For Each c In Selection
VTxt = VTxt & Application.WorksheetFunction.SUBSTITUTE(c.Value, Chr(10), "")
Next
Debug.Print VTxt 'oder vorgegebene Adresse, zB Range("B5").value = VTxt
End Sub

Das Ergebnis kannst du im Direktfenster sehen (Debug.print schreibt es dahin) und dort auch als String kopieren.

Einen wechselnden ZielOrt anzugeben, könnte bei einer Mehrfachauswahl schwierig werden, wenn Du die vorhandene Auswahl nicht verlieren willst.. (ginge alles, zB mit einer/twei zusätzlichen Variablen und einer Inputbox, aber doch etwas komplizierter)

Ich gehe mal davon aus, dass die Umbrüche nicht durch Chr(13) verursacht sind, sonst müsste/könnte man das auch noch Wechseln:

VTxt = Application.WorksheetFunction.Substitute(VTxt, Chr(13), "")


Da ich beim Verschachteln innerhalb einer Worksheetfunction bei Makros Fehler befürchte, denen ich im Moment nicht nachgehen möchte, würde ich das der Einfachheit halber in einer extra Zeile machen.

Kommentar von Suboptimierer ,

Statt 

Application.WorksheetFunction.Substitute(c.Value, Chr(10), "")

kannst du schreiben: 

Replace(c.Value, Chr(10), "")

VBA-Funktionen sind in der Regel schneller (und kürzer aufrufbar) als WorksheetFunctions.
_____________

Du kannst daraus auch eine Funktion machen, mit ZellinhalteVielfachAuswahlVerketten = VTxt. Dann lässt sich das Makro direkt im Sheet ausführen.


Kommentar von Iamiam ,

Danke für den Hinweis mit Replace! Muss mal die Liste der verfügbaren Funktionen suchen, damit ich nicht alles im Kopf behalten bzw verstreut aufschreiben muss.

Das mit der Funktion ist insofern problematisch, als ich beim Auswählen des Funktionsortes ja die Auswahl verliere! (müsste dann zwingend über den Bereichsnamen gehen)

"in einer extra Zeile": Mit Replace wäre das Verschachteln vllt einfacher!

Kommentar von Suboptimierer ,

Du könntest den Bereich als Parameter übergeben.

Wenn du so vorgehen willst, dass du den zu verkettenden Bereich markierst und dann verkettest, würde ich mir die Ergebniszelle vordefinieren oder den Text schon einmal in die Zwischenablage kopieren.

Dazu musst du die Bibliothek "Microsoft Forms 2.0 Object Library" hinzufügen.

  Dim clip As MSForms.DataObject

'...
Set clip = New MSForms.DataObject clip.SetText vTxt clip.PutInClipboard
Expertenantwort
von Suboptimierer, Community-Experte für Excel, 25

Entweder du schreibst dir ein Makro, welches du in deine personal.xlsb einfügst oder du kumulierst die Zeichenketten der Zellen.

kumulieren:

Wenn in A1:A100 die Zeichenketten stehen, dann in B1 "=A1" schreiben und in B2 "=B1&A2" schreiben. In die Ergebniszelle schreibst du 

=WECHSELN(WECHSELN(B100;ZEICHEN(10);"");ZEICHEN(13);"")

Makro:

Public Function Verketten3(ParamArray Bereich() As Variant) As String
  '
  ' Verkettet den Inhalt mehrerer Zellen
  '  ohne Trennzeichen
  '
  ' 18.04.2011 
  '
  Dim rSubbereich
  Dim rZelle
  Verketten3 = ""

For Each rSubbereich In Bereich If IsObject(rSubbereich) Then For Each rZelle In rSubbereich Verketten3 = Verketten3 & rZelle.Value2 Next End If Next End Function

Aufruf: 

=WECHSELN(WECHSELN(PERSONAL.XLSB!Verketten3(A1:A100);ZEICHEN(10);"");ZEICHEN(13);"")


Kommentar von Suboptimierer ,

Du kannst auch mit iterativer Berechnung verketten.

Datei → Optionen → Formeln → (x) Iterative Berechnung aktivieren

A: Spalte mit zu verkettenden Zeichenfolgen
C1: =WENN(D1=1;A1;C1&INDIREKT("A"&TEXT(D1;"0")))
D1: =D1+1

Einen Neustart von D1 bewirkst du, indem du in den Editiermodus von D1 gehst und Enter drückst.

Kommentar von FordPrefect ,

Sehr schön. Auch wenn ich mich ernsthaft frage, wozu man das braucht, ist das Makro sehr elegant gelöst.

Kommentar von Suboptimierer ,

Das eine oder andere Mal brauchte ich schon so eine Funktion. 

Am meisten wundert es mich, dass Microsoft nicht von Haus aus daran gedacht hat, dass man gerne Bereiche verketten können möchte.

Man hätte sich alles bei der Funktion SUMME abschauen können. Genau die kann das nämlich, nur mit einem anderen Operator.

Kommentar von Iamiam ,

ah, Du hast das als Funktion gelöst. Auch gut.

Das xl das nicht gemacht hat, liegt vermutlich daran, dass Matrixformeln keine Textfunktionen zulassen. Möglicherweise prinzipiell, vllt aber waren sie auch nur zu faul, das umzuschreiben oder sie haben sich das aufgehoben, um später Verbesserungen vermarkten zu können. Vllt gehen ja Textfunktionen bals als Matrixformel? Spätestens, wenn alle auf die Netz-abhängigen Versionen und zeitlich befristeten Abos upgedated haben?

Kommentar von Suboptimierer ,

Der Pferdefuß liegt bei der Verknüpfung von Zahlen und Texten. Wenn 0*Text = "" wäre und X*Text=Text sonst und wenn Text+Text = Text&Text wäre, wäre einem schon geholfen.

Antwort
von Walum, 27

=verketten(A1;" ";B1)

Kommentar von Nebelkrieger ,

und wenn ich das mit gefühlt 100 Zellen machen will ? gibt's da nicht was einfacheres bzw.effizienteres ?

Antwort
von Nebelkrieger, 21

C1: =A1&","&B1  ist nicht möglich da das zu aufwendig wäre

Keine passende Antwort gefunden?

Fragen Sie die Community