Frage in der Excelschulung: in drei Spalten stehen verteilt Titel | Vorname | Nachname.
Sie sollen verkettet werden.
Wendet man die Blitzvorschau an und schreibt in die erste Zelle Vorname und Nachname, dann fehlt der Titel:
Hat die erste Person einen Titel und trägt man alle drei Informationen ein, wird ein Leerzeichen bei den Personen ohne Titel eingefügt:
Die Blitzvorschau führt hier zu keinem brauchbaren Weg.
Die Teilnehmerin, die die Frage gestellt hatte, war sehr traurig.
Die Lösung für das Problem heißt beispielsweise: die Funktion „TEXTVERKETTEN“.
=TEXTVERKETTEN(" ";WAHR;C2:E2)
Wichtig ist der Wert WAHR beim Parameter Leer_ignorieren. So wird bei Personen, die keinen Titel haben, die leere Zelle nicht mit einem Leerzeichen verkettet, sondern übergangen.
Sehr geehrter Herr Martin, Heute habe ich folgende Frage. Wenn ich Wörter miteinander verketten möchte, habe ich bisher die Funktion Verketten genommen, wo ich auch zwischen 2 Wörtern ein Komma setzen konnte:
So weit, so gut. Nehme ich aber die Funktion Textverketten, funktioniert es nicht mit dem Komma:
Frage: Was müßte ich tun bei der letzten Funktion, um z. B. zwischen Nachname und Ort ein Komma zu setzen, dem ein Leerzeichen folgt?
####
Hallo Herr F.,
nein, mit TEXTVERKETTEN können Sie nicht mit unterschiedlichen Trennzeichen verketten. Der Gedanke ist, dass TEXTVERKETTEN mit Bereichen arbeitet:
=TEXTVERKETTEN(„; „;WAHR;A2:H2)
Wenn Sie einzeln verbinden möchten, müssen Sie auf das «&» zurückgreifen oder die alte VERKETTEN-Funktion verwenden.
Ich benutze TEXTVERKETTEN gerne, wenn ich eine Reihe an Mailadressen habe – dann verbinde ich sie zu einer Zeile:
=TEXTVERKETTEN(„;“;WAHR;A1:A555)
und kann sie so über Outlook in das An-Feld eintragen. DAS würde mit VERKETTEN nicht funktionieren.
Hallo, i ch benötige kurze Hilfe, ich habe hier ein Makro, noch von meinem Vorgänger, das wohl in einer Excel Liste eine Zeile entfernt und wo anders dann wieder einsetzen kann. Nun ist es aber so, das ich Zeilen habe, die einen Zeilenumbruch in der Zelle haben, und dann immer eine Fehlermeldung 1004 kommt. Können Sie mir hier BITTE helfen???? Danke Jörg
Hallo Jörg, so groß und schwierig kann das ja wohl nicht sein – wenn das Makro vorher gelaufen ist … schick es mir doch mal, bitte, dann schreibe ich dir etwas dazu. Liebe Grüße Rene
Hallo Rene,
hier mal so….
In einer Excel Zelle
habe ich einen Textumbruch, mit sehr viel Text.
Den kann ich zwar über
den Makro „löschen“, aber dann nciht
mehr an einer anderen
Stelle einfügen…..
BITTE Um HILFE! DANKE
Sub FahrtAuswaehlenEinfach()
Dim aDlg As DialogSheet
Dim aRow As Range
Set aDlg = Sheets("Fahrtauswahl")
i = 1
aDlg.ListBoxes("Fahrten").RemoveAllItems
Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
astring = astring + " - " + Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
astring = astring + " " + Sheets("Tabelle2").Cells(i, 4).Value
astring = astring + " --- " + Sheets("Tabelle2").Cells(i, 8).Value
astring = astring + " / " + Sheets("Tabelle2").Cells(i, 11).Value
aDlg.ListBoxes("Fahrten").AddItem astring
i = i + 1
Loop
If aDlg.Show = True Then
i = aDlg.ListBoxes("Fahrten").ListIndex
Sheets("Tabelle2").Rows(i).EntireRow.Cut
Sheets("Linienpläne - quer").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
End If
End Sub
Zuerst musst ich schlucken und mich auf die Suche machen. Was bitte ist ein DialogSheet? Ich werde fündig: in Excel 4.0 konnte man ein Dialogblatt einfügen. Und darauf Steuerelemente platzieren. Und die verwenden so etwas immer noch im Jahre 2020?
Egal. Meine Vermutung liegt beim Operator „+“. Wenn in den Zellen Zahlen stehen, hat VBA ein Problem beim Verketten von
Das ist aber nicht die Lösung. Ich bitte um die Daten. Der Zeilenumbruch ist nicht das Problem, sondern:
Der Text ist zu lang! Excel lässt mehr als 8.000 Zeichen / Zelle zu, die ListBox leider nur 255
Also schnell den Code geändert:
Sub FahrtAuswaehlen()
Dim aDlg As DialogSheet
Dim aRow As Range
Set aDlg = Sheets("Fahrtauswahl")
i = 1
aDlg.ListBoxes("Fahrten").RemoveAllItems
Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
astring = astring & " - " & Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
astring = astring & " " & Left(Sheets("Tabelle2").Cells(i, 4).Value, 200)
astring = astring & " --- " & Sheets("Tabelle2").Cells(i, 11).Value
aDlg.ListBoxes("Fahrten").AddItem astring
i = i + 1
Loop
If aDlg.Show = True Then
For i = aDlg.ListBoxes("Fahrten").ListCount To 1 Step -1
If aDlg.ListBoxes("Fahrten").Selected(i) Then
Sheets("Tabelle2").Rows(i).EntireRow.Cut
Sheets("Linienpläne - quer").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
End If
Next
End If
End Sub
Das Telefon klingelt. „Hallo René, ich bin’s: Angelika. Hast du mal nen Moment Zeit?“
Kennt ihr Gespräche, die so beginnen? „Hast du mal einen Moment Zeit?“ Das dauert normalerweise sehr, sehr lange. Egal – ich nehme mir die Zeit für Angelika.
„Jep, schieß los – was gibt’s?“ „Ach mein Excel nervt wieder! Ich bereite gerade eine Schulung vor. Excel zeigt mir immer nur die Formeln an, nicht die Ergebnisse. Ich bin völlig verzweifelt; ich weiß nicht, was ich machen soll!“ „Hast du die Formel-Anzeige eingeschaltet? – Formeln / Formeln anzeigen.“ „Ne, da habe ich schon geschaut.“ „Wie sind denn die Zellen formatiert? Wie lautet das Zahlenformat“ „Na – als Standard sind sie formatiert!“ „Mach mal einen Doppelklick auf die Zelle mit der Formel! Und dann [Enter]“ „Ach – jetzt geht es! Was war das?“ „Ich frag mal zurück: was hast du gemacht?“
„Also von vorne. Ich bereite gerade die Excelschulung für nächste Woche vor. Ich habe eine Liste mit Telefonnummern:
Ich füge Spalten ein und zeigen den Teilnehmern den Assistenten Daten / Text in Spalten.
Dann will ich die Teile wieder zusammenbauen. Und: egal, was ich verwendet habe: das &-Zeichen, VERKETTEN, TEXTKETTE, TEXTVERKETTEN … immer die Formel statt das Ergebnis!“
„Klar! Du hast die Telefonnummern als Text formatiert.“ Durch das Spalten Einfügen übernimmst du das Textformat. Und deshalb rechnet die Formel nicht mehr, sondern wird als Text eingefügt.“
„René – danke! Ich wusste doch, dass Excel nervt. Und dass du mir helfen kannst!“
Hallo Rene, ich mache zurzeit Ihre Ecel Schulung Tipps & Tricks, bis jetzt bin ich begeistert, vielen Dank. Eventuell können Sie mir mit einer Formel helfen? Ich habe zwei Tabellenblätter und möchte Emailadresse, die in Blatt 1 in Spalten aufgelistet sind, zu Blatt 2 einen automatischen Bezu herstellen, sodass dort in einer Zelle die EMailadressen automatisch erscheinen, wenn ich sie in Blatt 1 eingetippt habe. Ich danke bereits für Ihre Hilfe.
Hallo Anna, wenn sich der Cursor auf der Zelle befindet, wo die Email-Adresse stehen soll, genügt ein „=“, dann ein Klick auf das erste Tabellenblatt und ein zweiter Klick auf die Zelle, in der sich die Mailadresse befindet. Achtung: Nicht zurückklicken! Sondern mit [Enter] bestätigen! Dann steht in der Zielzelle beispielsweise =Tabelle1!C3 Hilft das? Oder denke ich zu einfach? schöne Grüße Rene Martin
Lieber Rene, danke für die supr schnelle Antwort. Es ist leider doch ein wenig komplizierter, aber ich denk für Sie ebenfalls problemlos zu lösen. Ich füge ein Beispieldokument ein zum besseren Verständnis. Aus Tabelle1 möchte ich automatische alle Email-adressen von Runde 1 in aufgelistet in ein Zelle im Tabellenblatt ‚Mailverteiler‘ auflisten. Wie lautet die Formel des Zellbezugs? Besten Dank für Ihre Hilfe.
Hallo Anna, hast du eine neueres Excel? Und dort die Funktion TEXTVERKETTEN? Damit geht es LG :: Rene
Heute in der Excelschulung. Ich zeige die Datenüberprüfung. Eine Artikelliste. In die Mengenspalte darf nur eine ganze Zahl eingetragen werden. Ein Teilnehmer fragt, ob man die Eingabe auch so begrenzen kann, dass die Gesamtmenge < 100 sein muss. Klar – kein Problem: Benutzerdefiniert – =SUMME(D:D)<100
Klappt.
Und dann möchte der Teilnehmer, dass die Eingabe nur ganze Zahlen akzeptiert. Leider kann man keine zwei Datenüberprüfungen miteinander verketten.
Man müsste die Formel erweitern, beispielsweise so:
Hallo. Ich brauche einen Tipp. Ich erhalten einige Male im Jahr eine Tabelle mit vielen Zahlen. Da die Zeilen keine eindeutige ID haben, habe ich mit der Funktion VERKETTEN mehrere Felder konkateniert (zusammengefasst), so dass ich eine ziemlich eindeutige ID habe. Ich wollte diese Formel weiter runterziehen, weil sich die Liste ab und zu erweitert. Jedoch – Excel zeigt in den leeren Zeilen Fehler. Das verstehe ich nicht: leer & leer & leer müsste doch leer sein, oder?
VERKETTEN – klappt gut, aber irgendwann mit Fehler
Die Antwort: Sie haben die Tabelle in eine „intelligente Tabelle“ verwandelt (Einfügen / Tabellen / Tabelle). Die Formel in der Zelle lautet:
also: Tabellenname Tabelle1[Spaltenname]. Das impliziert: gleiche Zeile wie Formelzelle.
Allerdings befindet sich keine Tabelle neben den neuen Zellen unterhalb der Tabelle. Wenn es Sie stört, können Sie um die Funktion die Formel WENNFEHLER bauen, also:
Ich habe eine Liste. darin befinden sich in einer Spalte Vornamen, in einer anderen Nachnamen. Das Verketten mit
=D2&E2
klappt hervorragend. Aber – darf ich denn kein Leereichen zwischen Vor- und Nachname schreiben. Ohne – das wäre schon ganz schön doof.
Verketten
Die Antwort: Natürlich geht es. Aber Sie dürfen nicht das Leerzeichen direkt eingeben, sondern müssen es als Leerzeichen kennzeichnen. Also so
=D2&“ „&E2
Dann funktioniert es. Und: Vergessen Sie nicht das zweite Verkettungszeichen „&“!
Übrigens: Ich finde es erstaunlich, dass Excel das Leerzeichen akzeptiert – eigentlich sind Leerzeichen in Formeln verboten und werden mit einer Fehlermeldung quittiert!
