Text und Zelleninhalt in einer Zelle ohne leere Zellen aufbauen

[Daniel Albert]

Hallo,

folgende Herausforderung. Ich möchte aus den folgenden Zeilenbereich einen Text zusammensetzen. Problem ist aber das wenn sich leere Zellen dazwischen befinden dannpasst der Text nicht mehr.

   

Es sollte der Text wie folgt zusammengesetzt werden: Kinderzulage für Ben & Martin.

Die restlichen & sollen dann nicht mehr angezeigt werden.

Aktuell ich folgende Formel eingebaut denke aber ich muss die Zellen vorher aufbereiten

Code:

="Kinderzulage von "&G36&" &"&G37&" &"&G38&" &"&G39&" &"&G40&" &"&G41

Über folgenden Link http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=43 geht es aber nicht da dann ja trotzdem noch Leerzeilen nach dem Inhalt "Kneipe" vorhanden sind.

Geht das überhaupt?

[shift-del]

Daniel Albert,

ja das geht.

	G	H	I	J
36	Ben	Ben
37		Ben
38	Martin	Ben  Martin
39		Ben  Martin
40		Ben  Martin
41		Ben  Martin
42
43	Kinderzulage für Ben & Martin
44	Kinderzulage für Ben & Martin

Zelle	Formel
H36	=G36
H37	=H36&" "&G37
H38	=H37&" "&G38
H39	=H38&" "&G39
H40	=H39&" "&G40
H41	=H40&" "&G41
G43	="Kinderzulage für "&TEXTVERKETTEN(" & ";WAHR;$G$36:$G$41)
G44	="Kinderzulage für "&WECHSELN(GLÄTTEN(G36&" "&G37&" "&G38&" "&G39&" "&G40&" "&G41);" ";" & ")

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[AlterDresdner]

Hallo Daniel,
zunächst die vermutlich korrekte (weil ungetestete) Version Deiner Formel:

Code:

="Kinderzulage von " & G36 & " " & G37 &" " & G38 & " " & G39 & " " & G40 &" " & G41

Wenn Du dann noch überflüssige Leerzeichen weghaben willst, ist es etwas länger (hier nur am Beispiel G39):

Code:

... G38 & WENN(G39 = ""; ""; " " & G39) & ...

Da war shift-del wieder schneller und besser...

[RPP63]

Moin!
Nur als Ergänzung mal eine Lösung per UDF (User defined function)
Modul Modul1

Option Explicit 
 
Function TxtKette(rng As Range) As String 
Dim arr, i# 
arr = rng.Value 
For i = 1 To Ubound(arr) 
  If Not IsEmpty(arr(i, 1)) Then _
    TxtKette = TxtKette & arr(i, 1) & " & " 
Next 
TxtKette = Left(TxtKette, Len(TxtKette) - 3) 
End Function 
 

Ergibt im Tabellenblatt:

	A	B
1	Peter	Zulage für Peter & Max & Paul
2
3	Max
4
5
6	Paul

Formeln der Tabelle

Zelle Formel B1 ="Zulage für "&TxtKette(A1:A6)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf

[Daniel Albert]

Verdammt wie immer, bei mir geht es nicht richtig:

Ich habe in der gelb markierten Zelle deine Formel eingetragen: ="Zulage für "&TxtKette(G36:G41) und deinen Code unter Modul2 eingetragen

so sieht jetzt das Ergebnis aus:


   

Warum ?

[RPP63]

Verdammt, wie immer! ;)
An einem Screenshot erkennt man den Fehler meist nicht.
Deine Testdatei?

Gruß Ralf

[Daniel Albert]

Ahh verdammt, Punkt gefunden. In den Zellen wo die Namen dann stehen findet eine Prüfung mit folgender Formel statt

=WENN(C36<"";A36;"")

Somit steht ja was in der Zelle

[RPP63]

Dann muss die Prüfung anders lauten:

Function TxtKette(rng As Range) As String
Dim arr, i#
arr = rng.Value
For i = 1 To Ubound(arr)
  If Not arr(i, 1) = vbNullString Then _
    TxtKette = TxtKette & arr(i, 1) & " & "
Next
TxtKette = Left(TxtKette, Len(TxtKette) - 3)
End Function

Hier mal eine sinnfreie Formel, um den Leerstring zu erzeugen:

	A	B	C
1	Peter	Peter	Zulage für Peter & Max & Paul
2
3	Max	Max
4
5
6	Paul	Paul

Formeln der Tabelle

Zelle Formel A1 =WENN(ISTLEER(B1);"";B1) C1 ="Zulage für "&TxtKette(A1:A6)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf

[Daniel Albert]

Juhu jetzt geht es, Danke

Geht aber sogar ohne die Zusatzspalte A1 oder muss ich die mit Istleer anlegen?

[Daniel Albert]

Hallo,

jetzt ist mir aufgefallen, wenn keine Kinder vorhanden sind dann steht in der Zeile: #wert

Nun habe ich folgende Formel eingebaut mit welcher es geht aber finde die sehr verschachtelt. Gibt es eine einfachere:

=WENN(ODER(Kunden!B8="Ja";UND(Kunden!B60="";Kunden!B61="";Kunden!B62="";Kunden!B63="";Kunden!B64="";Kunden!B65=""));"";"Kinderzulage für "&TxtKette(Kunden!B60:B65))

Ich habe es anders nicht hinbekommen.