Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Zellen trennen wieder zusammenfügen


Betrifft: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 22.09.2017 09:31:14

Hallo ihr Excelspezialisten,

ich möchte gerne die Spalte A in Tabelle1 trennen, wie in beigefügter Mappe dargestellt.

Nach Änderung der Daten sollen diese in Spalte L wieder zusammengefügt werden.
Gleiche Reihenfolge mit Punkt und Komma ect..

Leider bin ich mit meinen Versuchen gescheitert, daher bitte ich euch um Hilfe.

Mappe anbei: https://www.herber.de/bbs/user/116442.xlsm

Gruss
Peter

  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Phillieblunt
Geschrieben am: 22.09.2017 10:42:41

Servus,

kein Spezialist, vorab :)

Ich hätte es als Bastler so gelöst:
L2=C2&" "&D2&" "&E2&" "&F2&" "&G2&", "&H2&" "&I2&" "&J2

und dann Formel runterziehen


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 22.09.2017 10:53:34

Hallo,

besten Dank für Deine Antwort.

Aber ich benötige ein Makro wie die Trennung erfolgt, so wie ich diese in der Mustermappe dargestellt habe und nach einer noch durchzuführenden Änderung sollen die Daten wieder zusammengefügt werden per Makro.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Phillieblunt
Geschrieben am: 22.09.2017 11:11:58

Als weitere Anregung zum Basteln - sorry, mehr als selbst basteln kann ich nicht :)

https://www.herber.de/bbs/user/116443.xlsm

Vg Philipp


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 22.09.2017 13:57:23

Hallo Philipp,

besten Dank für Deine Hilfe.

Die Anregung mit der Verkettung ist zwar fehlerhaft aber ich werde testen, ob ich eine andere Lösung
daraus erstellen kann.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter(silie)
Geschrieben am: 22.09.2017 11:13:22

Hallo,

ich nehme an die Daten werden aus irgendeiner Datei importiert?

Da die Daten in der Spalte A nicht klar getrennt werden von einander
und es nicht möglich ist die Daten durch ihren Wert genau zu zuordnen,
funktioniert das nicht.

Wie soll ein Code denn erkennen ob "Fleischer" an Position xy in Zelle(x, 1)
jetzt ein Vor- Nachname, Beruf, Raum oder was auch immer ist?

Die Daten in Spalte A müssten z.B. so drinn stehen:
Frau; Dr.; Name; M.; (Beruf); Raum; 123

Wenn in der Zelle nicht alle Daten vorhanden sind, dann würde es so aussehen:
Frau;;Name; M.;;;

Somit ist die Position für die jeweiligen Daten klar und die werte können zugeordnet werden


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 22.09.2017 11:23:36

Hallo Peter,

besten Dank für Deine Antwort.

In einer Datei werden die Daten der Spalte A so wie ich sie Dir aufgelistet haben erfasst.

Nun sollen die Daten der bisherigen Datei in einer neuen Datei verwendet werden und es werden
Zeilen entfernt und neue Zeilen eingefügt.

Damit im Anschluss die Daten dem Namen nach sortiert werden können, benötige ich das heraustrennen
von zumindest Name und Teil von Vorname.

Das mit Deiner Auflistung, dass nach jedem Wortteil getrennt werden kann müsste das Semicolon zuvor eingefügt werden, jedoch ggf. zweimal bzw. dreimal.

Ich habe keine Ahnung wie das funktionieren soll.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: yummi
Geschrieben am: 22.09.2017 11:22:34

Hallo Peter,

wie du ja schon gelesen hast ist du Zuordnung so sehr "nebelig".
Um das ganze klarer zu machen, ein paar Fragen und Anmerkungen

1. Anrde ist immer vorhanden? (evtl. auf einem Hilfsblatt die erlaubten Anreden defineren, einfach als liste untereinander weg)
2. Das gleiche für die erlaubten Titel
3. Es folgt immer erst der nachname?
4. Es gibt mal einen Vornamen, mal nicht, korrekt?
5. Steht der Beruf immer in klammern?
6. Raum heisst immer Raum?
7. Raumnr ist klar
8. der raumzusatz ist mit leerzeichen abgetrennt?

Laut deiner Aufteilung werden die Kommata gelöscht,
Beim Zusammenfassen sollen sie wieder erscheinen, dann müssen diese ebenfalls in eienr seperaten spalte (kann ausgeblendet sein) separiert werden, wo überall können denn Kommata auftreten?

Also ohne eine zusätzliche Konfigtabelle, wird das ncihts mit dem Makro

Gruß
yummi


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 22.09.2017 11:43:49

Hallo yummi,

Du hast die Sache vollkommen richtig erfasst und definiert.

Zur Erläuterung:

Die Daten werden aus einer Datei übertragen in eine neue Datei. Da dort Zeilen entfernt bzw. neue eingefügt werden mit neuen Daten, muss die Tabelle dann bis auf die letzten zwei Zeilen nach Namen
sortiert werden.

1. Anrede ist immer vorhanden? ja - Herr oder Frau
2. Das gleiche für die erlaubten Titel ja wenn Titel vorhanden dann nach Anrede
3. Es folgt immer erst der nachname? ja es folgt immer erst Nachname
4. Es gibt mal einen Vornamen, mal nicht, korrekt? ja - erforderlich zum Sortieren falls Nachname doppelt vorhanden ist

5. Steht der Beruf immer in klammern? ja
6. Raum heisst immer Raum? ja
7. Raumnr ist klar ja
8. der raumzusatz ist mit leerzeichen abgetrennt? ja

Die Kommata befinden sich nur nach dem Namen bzw. wenn Beruf vorhanden nach Beruf.

Wenn dies in einer zusätzlichen KonfigTabelle erfolgen muss keinerlei Problem.

Besten Dank für Deine Hilfe.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Daniel
Geschrieben am: 22.09.2017 12:04:32

Hi

1. schreibe diesen Code in ein allgemeines Modul:
er wandelt den Text aus A2 so um, dass er das Semikolon als Trennzeichen an den richtigen Stellen einfügt:

Function SauberTrennen(txt As String) As String

Dim Erg(0 To 7) As String
Dim TT() As String
Dim T As Long

TT = Split(Replace(txt, ",", " ,"), " ")

'--- Anrede
Erg(0) = TT(0)

'--- Titel
If TT(1) = "Dr." Then
    Erg(1) = TT(1)
    T = 2
Else
    T = 1
End If

'--- Name
Erg(2) = TT(T)

'--- Vorname
For T = T + 1 To UBound(TT)
    If TT(T) = "," Or TT(T) Like "(*" Or TT(T) = "Raum" Then Exit For
    If Erg(3) = "" Then
        Erg(3) = TT(T)
    Else
        If TT(T) <> "" Then Erg(3) = Erg(3) & " " & TT(T)
    End If
Next

'--- Beruf
For T = 0 To UBound(TT)
    If TT(T) Like "(*" Then
        Erg(4) = TT(T)
        Exit For
    End If
Next

'--- Raum
For T = 0 To UBound(TT)
    If TT(T) = "Raum" Then
        Erg(5) = TT(T)
        If T + 1 <= UBound(TT) Then Erg(6) = TT(T + 1)
        If T + 2 <= UBound(TT) Then Erg(7) = TT(T + 2)
    End If
Next

SauberTrennen = Join(Erg, ";")
End Function
2. Schreibe in die Zelle B2 die Funktion: =SauberTrennen(A2)
ziehe die Formel soweit runter wie benötigt

3. kopiere die Spalte B und füge sie an gleicher Stelle als Wert ein

4. Wende auf die Spalte B die Funktion DATEN - DATENTOOLS - TEXT IN SPALTEN mit dem Semikolon als Trennzeichen an und wähle als Ziel die Zelle C2 (im Schritt 3)

Gruß Daniel


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Daniel
Geschrieben am: 22.09.2017 12:04:48

Hi

1. schreibe diesen Code in ein allgemeines Modul:
er wandelt den Text aus A2 so um, dass er das Semikolon als Trennzeichen an den richtigen Stellen einfügt:

Function SauberTrennen(txt As String) As String

Dim Erg(0 To 7) As String
Dim TT() As String
Dim T As Long

TT = Split(Replace(txt, ",", " ,"), " ")

'--- Anrede
Erg(0) = TT(0)

'--- Titel
If TT(1) = "Dr." Then
    Erg(1) = TT(1)
    T = 2
Else
    T = 1
End If

'--- Name
Erg(2) = TT(T)

'--- Vorname
For T = T + 1 To UBound(TT)
    If TT(T) = "," Or TT(T) Like "(*" Or TT(T) = "Raum" Then Exit For
    If Erg(3) = "" Then
        Erg(3) = TT(T)
    Else
        If TT(T) <> "" Then Erg(3) = Erg(3) & " " & TT(T)
    End If
Next

'--- Beruf
For T = 0 To UBound(TT)
    If TT(T) Like "(*" Then
        Erg(4) = TT(T)
        Exit For
    End If
Next

'--- Raum
For T = 0 To UBound(TT)
    If TT(T) = "Raum" Then
        Erg(5) = TT(T)
        If T + 1 <= UBound(TT) Then Erg(6) = TT(T + 1)
        If T + 2 <= UBound(TT) Then Erg(7) = TT(T + 2)
    End If
Next

SauberTrennen = Join(Erg, ";")
End Function
2. Schreibe in die Zelle B2 die Funktion: =SauberTrennen(A2)
ziehe die Formel soweit runter wie benötigt

3. kopiere die Spalte B und füge sie an gleicher Stelle als Wert ein

4. Wende auf die Spalte B die Funktion DATEN - DATENTOOLS - TEXT IN SPALTEN mit dem Semikolon als Trennzeichen an und wähle als Ziel die Zelle C2 (im Schritt 3)

Gruß Daniel


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 22.09.2017 13:49:17

Hallo Daniel,

das Trennen der Zellen funktioniert einwandfrei.

Nun noch zwei Bitten:

1. geht das ganze auch per Makro
2. ich bräuchte noch das wieder Zusammenfügen der Daten, wobei das Komma an der richtigen Stelle gesetzt werden muss.

Besten Dank für Deine Hilfe.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Daniel
Geschrieben am: 22.09.2017 14:03:37

Hi
Formeln per Makro einfügen kannst du dir mit dem Recorder aufzeichnen.
das Auftrennen mit TEXT IN SPALTEN ebenfalls.

außerdem ist mir noch nicht klar, wie die Werte hinterher wieder zusammengesetzt werden sollen.

Gruß Daniel


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 22.09.2017 14:08:55

Hallo Daniel,

ich werde das Ganze mit dem Makrorekorder bearbeiten. Soweit besten Dank.

Das Zusammensetzen erfolgt in der selben Weise wie das Trennen. Als Ergebnis muss wieder Spalte A
werden.

Wie bereits ausgeführt werden Teile dieser Auflistung geändert. Teilweise neue Zeilen eingefügt
und teilweise Zeilen gelöscht. Damit anschliessnd eine Sortierung erfolgen kann benötige ich u. a.
die Aufschlüsselung.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Daniel
Geschrieben am: 22.09.2017 14:21:14

Hi
wenn die Werte in genau gleicher Weise wieder zusammengesetzt werden sollen und du die Auftrennung nur brauchst, um die Werte zu sortieren oder gezielt zu löschen, dann lass die Werte doch in Spalte A einfach stehen, mach die Aufteilung in den Spalten daneben und berücksichtige eben die Spalte A beim Sortieren und Zeilen löschen einfach mit.
Dann sparst du dir das zusammensetzen.

Gruß Daniel


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 22.09.2017 15:50:07

Hallo Daniel,

leider so nicht möglich. Es werden ja auch Werte geändert in den Spalten, welche getrennt wurden und zum Schluss benötige ich dann die zusammengesetzten Daten.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Daniel
Geschrieben am: 22.09.2017 23:15:09

Naja, dann wieder ne Formel einfügen, die die Zellen zu einem Text zusammensetzt.
Die wurde dir ja hier schon gezeigt.
Per VBA kannst du dann auch diese Formel einfügen und sie dann durch ihre Werte ersetzen.

Gruß Daniel


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: UweD
Geschrieben am: 22.09.2017 12:13:50

Hallo


auch noch eine Lösung von mir


Sub Teilen()
    Dim TB, LR As Double, i As Double
    Set TB = Sheets("Tabelle1")
    
    LR = TB.Cells(TB.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte 
    
    TB.Cells(2, 3).Resize(LR - 1, 8).ClearContents
    TB.Cells(2, 1).Resize(LR - 1, 1).TextToColumns Destination:=TB.Range("C2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, 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), Array(6, 1), Array(7 _
        , 1)), TrailingMinusNumbers:=True
        
    For i = 2 To LR
        If Right(TB.Cells(i, 4), 1) <> "." Then 'Prüfen auf Dr. etc 
            TB.Cells(i, 4).Insert Shift:=xlToRight
        End If
        If TB.Cells(i, 5) <> "" Then
            TB.Cells(i, 5) = Replace(TB.Cells(i, 5), ",", "")
            If InStr(TB.Cells(i, 5), "(") > 0 Then
                TB.Cells(i, 5).Resize(1, 2).Insert Shift:=xlToRight
                
            ElseIf InStr(TB.Cells(i, 5), "Raum") > 0 Then
                TB.Cells(i, 5).Insert Shift:=xlToRight
            End If
        End If
        If TB.Cells(i, 6) <> "" Then
            TB.Cells(i, 6) = Replace(TB.Cells(i, 6), ",", "")
            If InStr(TB.Cells(i, 6), "(") > 0 Then
                TB.Cells(i, 6).Insert Shift:=xlToRight
                
            ElseIf InStr(TB.Cells(i, 6), "Raum") > 0 Then
                TB.Cells(i, 6).Resize(1, 2).Insert Shift:=xlToRight
            End If
        End If
        If TB.Cells(i, 7) <> "" Then
            TB.Cells(i, 7) = Replace(TB.Cells(i, 7), ",", "")
            If InStr(TB.Cells(i, 7), "Raum") > 0 Then
                TB.Cells(i, 7).Insert Shift:=xlToRight
            End If
        End If

    Next
End Sub

Sub Zusammen()
    Dim TB, LR As Double
    Set TB = Sheets("Tabelle1")
    
    LR = TB.Cells(TB.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte 
    
    With TB.Cells(2, 12).Resize(LR - 1, 1)
        .ClearContents
        .FormulaR1C1 = _
            "=SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(RC[-9],"" "",RC[-8],"" "",RC[-7],"" "",RC[-6],"", "",RC[-5],"", "",RC[-4],"" "",RC[-3],"" "",RC[-2])),"", , "","", ""),""  "","" "")"
        .Value = .Value
    End With
End Sub

in ein Modul

Erst Teilen ausführen

nach der Änderung dann das 2. Makro ausführen


Feintunig bei den neuen Kommas ggf erforderlich



LG UweD


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 22.09.2017 13:18:03

Hallo Uwe,

besten Dank für Deine Hilfe.

Es funktioniert ausser den Leerstellen nach dem Name und die Kommata.

Kann dies noch berücksichtigt werden per Marko?

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 24.09.2017 09:41:47

Hallo Uwe, hallo ihr Excelspezialisten,

ich habe die Tabelle nach den Vorgaben von Uwe durchgeführt. Das Teilen gemäss Makro "Teilen" funktioniert einwandfrei.

Bezüglich dem wieder Zusammenfügen mittels Makro "Zusammen" tauchen deutliche Fehler auf.(Spalte L)

Ich habe selbst ein Makro konstuiert "Zusammen_fügen" auch hier ähnliche Fehler.(Spalte M)

Uwe führte dann an es fehlt noch an Feintuning. Mir fehlt jedoch jeglicher Ansatz, wie ich dies
lösen kann.

Im Endergebnis sollen dann die Spalten L und M aussehen, wie Spalte A.

Die Spalte A gibt wider, welche Möglichkeiten innerhalb der Tabelle möglich sind.

Musterdatei anbei: https://www.herber.de/bbs/user/116468.xlsm

Besten Dank für eure Hilfe.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: yummi
Geschrieben am: 25.09.2017 09:40:24

Hallo Peter,

ich habe bei der Teilen Funktion eine weitere Funktionalität hinzugefügt und merke mir jetzt wo ein Komma war. Die neue Funktionalität nutze ich beim Zusammenfügen
Nimm mal bitte folgenden Code und probier mal

Sub Teilen()
    Dim TB, LR As Double, i As Double
    Set TB = Sheets("Tabelle1")
    
    LR = TB.Cells(TB.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte
    
    TB.Cells(2, 3).Resize(LR - 1, 8).ClearContents
    TB.Cells(2, 1).Resize(LR - 1, 1).TextToColumns Destination:=TB.Range("C2"), DataType:= _
xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, 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), Array(6, 1),  _
Array(7 _
        , 1)), TrailingMinusNumbers:=True
        
    For i = 2 To LR
        If Right(TB.Cells(i, 4), 1) <> "." Then 'Prüfen auf Dr. etc
            TB.Cells(i, 4).Insert Shift:=xlToRight
        End If
        If TB.Cells(i, 5) <> "" Then
            If InStr(1, TB.Cells(i, 5).Value, ",", vbTextCompare) <> 0 Then
                TB.Cells(i, 2).Value = TB.Cells(i, 2).Value & "5"
            End If
            TB.Cells(i, 5) = Replace(TB.Cells(i, 5), ",", "")
            If InStr(TB.Cells(i, 5), "(") > 0 Then
                TB.Cells(i, 5).Resize(1, 2).Insert Shift:=xlToRight
                
            ElseIf InStr(TB.Cells(i, 5), "Raum") > 0 Then
                TB.Cells(i, 5).Insert Shift:=xlToRight
            End If
        End If
        If TB.Cells(i, 6) <> "" Then
            If InStr(1, TB.Cells(i, 6).Value, ",", vbTextCompare) <> 0 Then
                TB.Cells(i, 2).Value = TB.Cells(i, 2).Value & "6"
            End If
            TB.Cells(i, 6) = Replace(TB.Cells(i, 6), ",", "")
            If InStr(TB.Cells(i, 6), "(") > 0 Then
                TB.Cells(i, 6).Insert Shift:=xlToRight
                
            ElseIf InStr(TB.Cells(i, 6), "Raum") > 0 Then
                TB.Cells(i, 6).Resize(1, 2).Insert Shift:=xlToRight
            End If
        End If
        If TB.Cells(i, 7) <> "" Then
            If InStr(1, TB.Cells(i, 7).Value, ",", vbTextCompare) <> 0 Then
                TB.Cells(i, 2).Value = TB.Cells(i, 2).Value & "7"
            End If
            TB.Cells(i, 7) = Replace(TB.Cells(i, 7), ",", "")
            If InStr(TB.Cells(i, 7), "Raum") > 0 Then
                TB.Cells(i, 7).Insert Shift:=xlToRight
            End If
        End If

    Next
End Sub


Sub Zusammenfuehren_mitKomma()
    Dim TB, LR As Double, i As Double
    Dim s As Integer
    Dim strGesamt As String
    Set TB = Sheets("Tabelle1")
    
    LR = TB.Cells(TB.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte

    For i = 2 To LR
        strGesamt = ""
        For s = 3 To 10
            If s = 3 Then
                strGesamt = strGesamt & TB.Cells(i, s).Value
            Else
                strGesamt = strGesamt & " " & TB.Cells(i, s).Value
            End If
            If InStr(1, TB.Cells(i, 2).Value, CStr(s), vbTextCompare) <> 0 Then
                strGesamt = strGesamt & ","
            End If
        Next s
        TB.Cells(i, 12).Value = strGesamt
    Next i
End Sub
Gruß
yummi


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 25.09.2017 10:51:52

Hallo Yummi,

vielen Dank für Deine Hilfe. Du hast Dir wirklich viel Arbeit gemacht.

Leider bestehen noch ein paar kleine Fehler.

In Zeile 5 und 6 sind die Kommata falsch. Sie sind hinter dem Namen und nicht hinter (Beruf).
Ausserdem sind teilweise zu viele Leerzeichen dazwischen und am Ende.

Da diese Arbeit teil eines anderen Programms sind benötige ich Identität.

Ich habe mir da noch eine weitere Variante ausgedacht:

Die funktioniert wie folgt:

Mit diesem Makro füge ich die Spalten C bis J zusammen:

Sub Zusammen_fügen()

Dim rng As Range

For Each rng In Sheets("Tabelle4").Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
   

Cells(rng.Row, 12).Select

Cells(rng.Row, 12) = Sheets("Tabelle4").Cells(rng.Row, 3) & " " & _
Sheets("Tabelle4").Cells(rng.Row, 4) & " " & _
Sheets("Tabelle4").Cells(rng.Row, 5) & " " & _
Sheets("Tabelle4").Cells(rng.Row, 6) & " " & _
Sheets("Tabelle4").Cells(rng.Row, 7) & " " & _
Sheets("Tabelle4").Cells(rng.Row, 8) & " " & _
Sheets("Tabelle4").Cells(rng.Row, 9) & " " & _
Sheets("Tabelle4").Cells(rng.Row, 10)

Next rng

End Sub
Auch hier ergeben sich diverse Leerzeichen.

In der Spalte M füge ich diese Formel ein: =LINKS(L2;FINDEN("Raum";L2)-1) und kopiere sie herunter.

In den Zeilen, in welche kein Wort Raum existiert kommt Fehlermeldung #Wert!. Diesen muss ich noch
beseitigen.

Ich kopiere dann die Spalte M und füge die Inhalte in Spalte O ein.

In Spalte N füge ich die Formel ein: =RECHTS(L7;LÄNGE(L7)+1-FINDEN("Raum";L7)) und kopiere sie herunter.

Ich kopiere dann die Spalte N und füge die Inhalte in Spalte P ein.

In die Spalte Q füge ich die Formel ein: =WECHSELN(O7;" ";"") und kopiere sie herunter.

Diese Spalte Q kopiere ich und füge die Inhalte in Spalte R ein.

In Spalte S füge ich die Formel ein: =WECHSELN(Q7;" ";",")&"," und kopiere sie herunter.

Diese Spalte S kopiere und füge die Inhalte in Spalte T ein.

In Spalte U füge ich die Formel ein: =T7 & " " & P7 und kopiere sie herunter.

Ergebnis passt genau!!!

Nun benötige ich Deine Hilfe nochmals.

Diesen ganzen Vorgang möchte ich in ein Makro einfügen. Ich habe jedoch keine Ahnung,

Wie ich die Formeln per Makro in die Tabelle einfüge d. h. ich weiss nicht, wie ich die Formel in
VBA übersetze.

Danke für Deine Hilfe.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 25.09.2017 11:23:21

Hallo Yummi,

die beiden Makros von Uwe sind perfekt. Sie bringen genau das Ergebnis, wie ich mir das vorgestellt habe. Du brauchst daher das von mir gewünschte Makro nicht mehr aufbauen.

Vielen, vielen Dank für Deine Hilfe.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: UweD
Geschrieben am: 25.09.2017 09:47:40

Hallo nochmal


wenn die Kommas so wichtig sind (obwohl nicht einheitlich gesetzt) dann sollten die in den Feldern erhalten bleiben.

Da sie hinten stehen, dürften sie das Sortieren auch nicht stören,

- Ich hab das Makro Trennen entsprechend abgeändert.

- Beim Zusammensetzen wird dann einfach nur ein Leerzeichen zwischen den Feldern eingefügt.

Durch das Trim() werden doppelte Leerzeichen gelöscht (und Welche, die am Ende stehen)

Was dabei noch offen ist, Leerzeichen, die bereits in Spalte A am Ende vorhanden sind, werden so auch gelöscht. Siehe Zeile 6 und 11.
Dürfen die Weg?

Sub Teilen()
    Dim TB, LR As Double, i As Double
    Set TB = Sheets("Tabelle1")
    
    LR = TB.Cells(TB.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte 
    
    TB.Cells(2, 3).Resize(LR - 1, 8).ClearContents
    TB.Cells(2, 1).Resize(LR - 1, 1).TextToColumns Destination:=TB.Range("C2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, 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), Array(6, 1), Array(7 _
        , 1)), TrailingMinusNumbers:=True
        
    For i = 2 To LR
        If Right(TB.Cells(i, 4), 1) <> "." Then 'Prüfen auf Dr. etc 
            TB.Cells(i, 4).Insert Shift:=xlToRight
        End If
        If TB.Cells(i, 5) <> "" Then
            If InStr(TB.Cells(i, 5), "(") > 0 Then
                TB.Cells(i, 5).Resize(1, 2).Insert Shift:=xlToRight
                
            ElseIf InStr(TB.Cells(i, 5), "Raum") > 0 Then
                TB.Cells(i, 5).Insert Shift:=xlToRight
            End If
        End If
        If TB.Cells(i, 6) <> "" Then
            If InStr(TB.Cells(i, 6), "(") > 0 Then
                TB.Cells(i, 6).Insert Shift:=xlToRight
                
            ElseIf InStr(TB.Cells(i, 6), "Raum") > 0 Then
                TB.Cells(i, 6).Resize(1, 2).Insert Shift:=xlToRight
            End If
        End If
        If TB.Cells(i, 7) <> "" Then
            If InStr(TB.Cells(i, 7), "Raum") > 0 Then
                TB.Cells(i, 7).Insert Shift:=xlToRight
            End If
        End If

    Next
End Sub



Sub Zusammen()
    Dim TB, LR As Double
    Set TB = Sheets("Tabelle1")
    
    LR = TB.Cells(TB.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte 
    
    With TB.Cells(2, 12).Resize(LR - 1, 1)
        .ClearContents
        .FormulaR1C1 = _
            "=Trim(CONCATENATE(RC[-9],"" "",RC[-8],"" "",RC[-7],"" "",RC[-6],"" "",RC[-5],"" "",RC[-4],"" "",RC[-3],"" "",RC[-2]))"
        .Value = .Value
    End With
End Sub

LG UweD


  

Betrifft: AW: funktioniert von: Peter
Geschrieben am: 25.09.2017 11:21:04

Hallo Uwe,

vielen, vielen Dank für Deine Hilfe. Dein Makro funktioniert einwandfrei. Das mit den Leerstellen ist einwandfrei.

Genau so wie ich mir das vorgestellt habe.

Viele Grüsse
Peter


  

Betrifft: Prima! Danke für die Rückmeldung. owT von: UweD
Geschrieben am: 25.09.2017 11:54:19




  

Betrifft: Es funktioniert, falls die Daten tatsächlich nur … von: Luc:-?
Geschrieben am: 25.09.2017 21:45:19

…so vorliegen, wie es die Testdaten suggerieren, Peter & Uwe;
sind sie komplexer, d.h., können auch andere Namensformen vorkommen, kann es auch mal schief gehen, wie folgendes Bsp zeigt:

 ABCDEFGHIJKL
1
Name Gesamt: AnredeTitelNameVornameBerufRaumR-NrR-Zusatz Name Gesamt (ergänzt):
Herr Dr. Alpha, Raum 101 HerrDr.Alpha,  Raum101  Herr Dr. Alpha, Raum 101
Herr Beta Herr Beta      Herr Beta
Frau Gamma, Raum 006 Frau Gamma,  Raum6  Frau Gamma, Raum 6
Herr Delta (aBeruf), Raum 112 a Herr Delta (aBeruf),Raum112a Herr Delta (aBeruf), Raum 112 a
Frau Dr. Epsilon (bBeruf), Raum 123 FrauDr.Epsilon (bBeruf),Raum123  Frau Dr. Epsilon (bBeruf), Raum 123
Herr Vau M., Raum 101 Herr VauM., Raum101  Herr Vau M., Raum 101
Herr Dr. Zeta, Raum 103 HerrDr.Zeta,  Raum103  Herr Dr. Zeta, Raum 103
Herr Vau N. (cBeruf), Raum 112 a Herr VauN.(cBeruf),Raum112a Herr Vau N. (cBeruf), Raum 112 a
Frau Dr. Eta M. (dBeruf), Raum 123 FrauDr.EtaM.(dBeruf),Raum123  Frau Dr. Eta M. (dBeruf), Raum 123
Herr MDir. v.Raumann HerrMDir.   v.Raumann   Herr MDir. v.Raumann
Frau Prof. von der Heiden (eBeruf) FrauProf.vonderHeiden(eBeruf)   Frau Prof. von der Heiden (eBeruf)
2
3
4
5
6
7
8
9
10
11
12

Das war natürlich zu erwarten und evtl Nachnutzer seien vorgewarnt, dass hierfür spezielle Bedingungen vorliegen müssen, die nicht universalisierbar sind.
Da das Thema aber interessant ist, habe ich einmal versucht, die QuellDatenForm zu analysieren. Dabei ergaben sich unter Berücksichtigung der gemachten Aussagen folgende Prämissen:
1. Ein vollständiger DatenSatz muss sich in maximal 8 Teile trennen lassen.
2. Diese 8 Teile setzen sich iW aus 2 Gruppen @ 4 Teile zusammen, die günstigenfalls durch ( bzw Komma getrennt vorliegen.
3. Liegen beide nicht vor, wurde nur die erste Gruppe angelegt.
Daraus lassen sich folgende Schemata ableiten (X=stets vorhanden, Ø=kann uU fehlen):
a) nur 1.Gruppe   XØXØ ØØØØ
b) beide Gruppen XØXØ ØXXØ
Folglich sollten diese Gruppen auf eine einheitliche Form gebracht wdn:
c) XØXØ XØØØ
Das kann erreicht wdn, indem im Fall des Fehlens von Komma ein solches, und danach im Falle des Fehlens von (…) ein () hinzugefügt wird. Damit bildet ( einen GruppenTrenner und (…), (mit oder ohne Inhalt) das 1.Element der 2.Gruppe, das dann auf (…) verkürzt wdn kann.
In der 1.Gruppe bleiben weitere Unwägbarkeiten bestehen, deren hier wichtigste das unbestimmte 2.Element ist. Dieses muss deshalb, falls angelegt, bestimmten Normen folgen, d.h., es darf nur ein ungetrennter Text sein, der mit Pkt abgekürzt wurde (hier zB Dr.). Eine weitere Unwägbarkeit ist die Form des PersonenNamens (dazu im Bsp und darunter mehr). Das alles berücksichtigt, könnte das mit UDF-unterstützten Fmln so aussehen:
 ABCDEFGHIJKL
1
Name Gesamt: AnredeTitelNameVornameBerufRaumR-NrR-Zusatz Name Gesamt (ergänzt):
Herr Dr. Alpha, Raum 101 HerrDr.Alpha ()Raum101  Herr Dr. Alpha, Raum 101
Herr Beta Herr Beta ()    Herr Beta,
Frau Gamma, Raum 006 Frau Gamma ()Raum006  Frau Gamma, Raum 006
Herr Delta (aBeruf), Raum 112 a Herr Delta (aBeruf)Raum112a Herr Delta (aBeruf), Raum 112 a
Frau Dr. Epsilon (bBeruf), Raum 123 FrauDr.Epsilon (bBeruf)Raum123  Frau Dr. Epsilon (bBeruf), Raum 123
Herr Vau M., Raum 101 Herr VauM.()Raum101  Herr Vau M., Raum 101
Herr Dr. Zeta, Raum 103 HerrDr.Zeta ()Raum103  Herr Dr. Zeta, Raum 103
Herr Vau N. (cBeruf), Raum 112 a Herr VauN.(cBeruf)Raum112a Herr Vau N. (cBeruf), Raum 112 a
Frau Dr. Eta M. (dBeruf), Raum 123 FrauDr.EtaM.(dBeruf)Raum123  Frau Dr. Eta M. (dBeruf), Raum 123
Herr MDir. v.Raumann HerrMDir.v.Raumann ()    Herr MDir. v.Raumann, Raum
Frau Prof. `von der Heiden´ (eBeruf) FrauProf.von der Heiden (eBeruf)    Frau Prof. von der Heiden (eBeruf), Raum
C2:J2[;C3:J11]: {=SplitVx(VJoin(SplitVx(SplitVx(WECHSELN(WENN(RECHTS(PickOn(A2;2))=".";A2;WECHSELN(A2;" ";" ";1))&WAHL(VLike(A2;"*,*")+1;", ";"");",";WAHL(VLike(A2&WAHL(VLike(A2;"*,*")+1;", ";"");"*(*)*")+1;" (),";","));"(";-1;2);{" ".", "};;4);;-2))}
Alternative, falls leerzeichen-getrennte, mehrgliedrige Namen auftreten können (zuvor Markierungen setzen!)L2[:L7]: {=WECHSELN(VJoin(WECHSELN(WECHSELN(C2:J2;")";"),");"()";"");;-1);" ,";",")}
C12:J12: {=SplitVx(VJoin(SplitVx(SplitVx(WECHSELN(WECHSELN(WENN(RECHTS(PickOn(A12;2))=".";A12;WECHSELN(A12;" ";" ";1));MaskOn(WENN(RECHTS(PickOn(A12;2))=Alternative mit Autoeinfügen von Raum
              ".";A12;WECHSELN(A12;" ";" ";1));"mrk`´");WECHSELN(MaskOn(MaskOn(WENN(RECHTS(PickOn(A12;2))=".";A12;WECHSELN(A12;" ";" ";1));"mrk`´");"alf");" ";ZEICHENL8[:L12]: {=WECHSELN(VJoin(WECHSELN(WECHSELN(C8:I8;")";"), Raum");"()";"");;1);" ,";",")&" "&J8}
              (160)))&WAHL(VLike(A12;"*,*")+1;", ";"");",";WAHL(VLike(A12&WAHL(VLike(A12;"*,*")+1;", ";"");"*(*)*")+1;" (),";","));"(";-1;2);{" ".", "};;4);;-2))}Hierbei wird von bezweckter Ergänzung der RaumNr ausgegangen!
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Hier wurde in den beiden dem Original hinzugefügten QuellDatenZeilen mit weiteren NamensMöglichkeiten experi­mentiert, wobei sich die 1. dieser Zeilen noch in die Fml einfügt, sofern es sich beim Namenszusatz von um ein AdelsPrädikat handelt, das üblicher­weise, zur Unterscheidung von normalem „bürgerlichem“ von stets abgekürzt wird. Ist ein solches Prädikat kom­plexer, ist es kom­plexeren „bürgerlichen“ Namen (wie in der letzten Zeile) gleich­zusetzen (seit der Weimarer Republik sind solche „Titel“ ohnehin nur noch Namens­Bestand­teil). Diese müssen unbedingt zuvor markiert (bzw die in ihnen enthaltenen Leer­Zeichen durch geschützte - Zeichen160 - ersetzt) wdn, weil sonst alles durch­ein­ander kommt (die hierfür ver­wendete längere Fml kann auch generell ange­wendet wdn!).
Auf eine Ausgabe der RaumNr als Zahl wurde verzichtet, weil die TextTeile ja ohnehin zum Schluss wieder zusammen­geführt wdn sollen (das vorgegebene ZahlenFormat wurde im 1.Bsp ohnehin nicht berück­sichtigt, was dann zu unge­wollten Darstellungs­Fehlern im ergänzten EndText führt).
Anschließend können die getrennten TextTeile kopiert und als Werte auf die FmlZellen gespeichert wdn, was auch generell mit einer VBA-SubProzedur erledigt wdn könnte. Die Zusammenführung der dann ergänzten TextTeile ist dann recht einfach möglich. Die hier verwendete Fml berücksichtigt auch, dass bestimmte Teile nicht ergänzt wurden (enfernt leere Klammern, aber kein einsames End-Komma!). In der 2.Variante dieser Fml wird auch grundsätzlich noch Raum nach dem Komma ergänzt. Allerdings fehlt auch in dieser Variante die Formatierung der RaumNr, weil dieses Format nicht generell für alle Teile gilt und deshalb ebenfalls nur mit einer UDF aus der ZellDarstellung ausgelesen wdn müsste. Dann könnte diese Spalte ggf auch mit 000 formatiert wdn. Die Anwendung von TEXT wäre hier nämlich nicht möglich. Der Einfachheit halber sollten Ergänzungen und Korrekturen der RaumNr dann mit führendem TextPräfix (Standard: ') geschrieben wdn. Anderen falls müssten die beiden FmlVarianten so aussehen:
L2[:L7]: {=WECHSELN(VJoin(WECHSELN(WECHSELN(GetText(C2:J2);")";"),");"()";"");;-1);" ,";",")}
L8[:L12]: {=WECHSELN(VJoin(WECHSELN(WECHSELN(GetText(C8:J8);")";"), Raum");"()";"");;1);" ,";",")&" "&J8}


Die verwendeten UDFs sind im Archiv enthalten:
GetText (Vs1.1) https://www.herber.de/forum/archiv/1540to1544/t1542024.htm#1542082
MaskOn (Vs2.1) https://www.herber.de/cgi-bin/callthread.pl?index=1344962#1345181
PickOn (Vs1.1) https://www.herber.de/forum/archiv/1140to1144/1141994_Teilstring_aus_String_entfernen.html#1142025 (Folgebeiträge m.Korrekturen beachten!)
SplitVx (Vs1.6)* https://www.herber.de/cgi-bin/callthread.pl?index=1301785#1301987 (Thread) https://www.herber.de/bbs/user/84157.xls (BspDatei; Vs ohne das hier wichtige Arg4 enthält wie akt Vs2.0 noch einen Fehler, der hier so nicht wirksam wird → deshalb zZ keine VssAktualisierung mögl)
VJoin (Vs1.4) https://www.herber.de/bbs/user/99024.xlsm
VLike (Vs1.2 o.VPairs) https://www.herber.de/cgi-bin/callthread.pl?index=1247144#1247699
* Das in dieser Version fehlende 4.Argument bewirkt eine Normierung des erzeugten Vektors auf die angegebene (maximale) ElementeAnzahl. Dieses müsste hier ggf durch weitere FmlTeile bzw -Ergänzungen bewirkt wdn!

Bei solchen, uU stets wiederkehrenden Arbeiten ist eine SubProzedur natürlich sinnvoller, weshalb ich meine Fmln auch nicht unbe­dingt zur Anwendung empfehle (zumal die SplitVx-Version im Archiv hier auch nicht reicht). Ich wollte allerdings darauf hinge­wie­sen haben, dass ein solches Problem idR komplexer ist (oder sich im Nachhinein derartig entpuppt) als ursprünglich gedacht… ;-]
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: du hast völlig recht... von: Peter
Geschrieben am: 26.09.2017 09:31:59

Hallo Luc,

mit Deiner Feststellung. Und auch das Zahlenformat am Ende ist nicht korrekt.

Könntest Du mir das Ganze bitte mal als Datei zukommen lassen?!

Und wäre es für Dich ein grosses Problem, mir dies in einer SubProzedur einzubauen?

Das wäre wirklich toll.

Besten Dank

Gruss
Peter


  

Betrifft: Muss mal sehen, ob ich dafür Zeit finde, ... von: Luc:-?
Geschrieben am: 27.09.2017 01:25:05

…Peter,
denn ich habe noch andere Projekte fertigzustellen…
Übrigens, einen Fehler habe ich in der 2. separaten Fml (mit GetText) vergessen zu korrigieren → der AdressBereich muss natürlich ebenso lauten wie in der Tabelle, also GetText(C8:I8)! Das sich der von der 1.Fml unterscheiden muss, liegt an den Spezifika der UDF VJoin → mit 3.Argument=1 wdn auch TeilTexte auf Wiederholung geprüft (und a kann ja leicht im bis dahin kombinierten Text auftreten!), während bei -1 nur der ganze Text vgln wird (dann würde aber ein zusätzliches Raum drin bleiben, weil es zum ZeitPkt des Vgls kein Einzelwort mehr ist!).
Aber anhand meiner Analyse und LösungsVorschläge sollten das auch andere, wie bspw UweD & Yummi schaffen können…
Morrn, Luc :-?


  

Betrifft: AW: wäre super ... von: Peter
Geschrieben am: 27.09.2017 07:58:54

Danke Luc.

Gruss
Peter


  

Betrifft: So, habe mal meine FormelLösung überarbeitet, ... von: Luc:-?
Geschrieben am: 28.09.2017 03:23:46

…Peter,
wobei ich den UDF-Einsatz auf nur 3 aus dem Archiv reduziert habe. Dafür gelten natürlich immer noch bestimmte Bedingungen:
1. Der QuellText muss sich stets in maximal 8 Teile trennen lassen, die mit den ÜberarbeitungsSpalten kor­respon­dieren.
2. Der Text besteht aus 1-2 TextGruppen zu je 4 Teilen, die den bereits zuvor genannten Schemata bzgl ihrer Bele­gung ent­spre­chen, d.h., der GrupenTrenner ist ( oder Komma.
3. Ein evtl Titel steht an 2.Stelle und ist mit Punkt abgekürzt.
4. Ein evtl Vorname steht nach dem Namen und ist ebenfalls mit Punkt abgekürzt (Stellenzahl beliebig).
5. Eine Markierung mehrgliedriger (leerzeichengetrennter) Namen ist so (4.) nicht (mehr) erforderlich.
Für TrennFml und Rekombination der überarbeiteten TeilTexte gilt Folgendes:
6. Die TrennFml ist eine plurale MatrixFml, die sich automatisch (bei Abschluss als solche) über alle zuvor markierten (8) Spalten einer Zeile erstreckt.
7. Die Leerzeichen in ggf festgestellten mehrgliedrigen Namen wdn durch geschützte Leerzeichen ersetzt, die auch bei Rekom­bi­na­tion erhalten bleiben, d.h., der Name kann nicht mehr auto­ma­tisch getrennt wdn.
8. Vor der Überarbeitung müssen die entsprd Zeilen und Spalten mit ihren ErgebnisWerten überschrieben wdn; die Spalte des Rau­mes darf dann bei der Überarbeitung ggf leer bleiben.
9. Bei Rekombination entfallen leere Zellen inkl leerer KlammerPaare, aber Komma wird stets gesetzt, gefolgt von den rest­lichen vorhandenen Begriffen, wobei diese einzeln angesprochen wdn, was die Anwen­dung von TEXT anstelle der UDF GetText und somit normale Fmln ermög­licht. Dem Komma folgt, im Falle der Leere der entsprd Zelle, zusätzlich das Wort Raum.
Ich habe nun keine superlangen EinzelFmln entwickelt, sondern ein ganzes Fml-System geschaffen, das zwar ohne HilfsZellen auskommt, dafür aber benannte Fmln (Fmln, die unter einem Namen im Namens­Manager definiert wurden; auch längere) benutzt. Diese bauen aufeinander auf und lösen so die Aufgabe stufen­weise (dazu mehr ganz unten). Das Ergebnis einer richtigen Anwendung dieses Fml-Systems, hier natürlich ohne Überarbeitung, sähe dann so aus:

 ABCDEFGHIJKL
1
Name Gesamt: AnredeTitelNameVornameBerufRaumR-NrR-Zusatz Name Gesamt (ergänzt):
Herr Dr. Alpha, Raum 101 HerrDr.Alpha ()Raum101  Herr Dr. Alpha, Raum 101
Herr Beta Herr Beta ()    Herr Beta, Raum
Frau Gamma, Raum 006 Frau Gamma ()Raum006  Frau Gamma, Raum 006
Herr Delta (aBeruf), Raum 112 a Herr Delta (aBeruf)Raum112a Herr Delta (aBeruf), Raum 112 a
Frau Dr. Epsilon (bBeruf), Raum 123 FrauDr.Epsilon (bBeruf)Raum123  Frau Dr. Epsilon (bBeruf), Raum 123
Herr Vau M., Raum 101 Herr VauM.()Raum101  Herr Vau M., Raum 101
Herr Dr. Zeta, Raum 103 HerrDr.Zeta ()Raum103  Herr Dr. Zeta, Raum 103
Herr Vau N. (cBeruf), Raum 112 a Herr VauN.(cBeruf)Raum112a Herr Vau N. (cBeruf), Raum 112 a
Frau Dr. Eta M. (dBeruf), Raum 123 FrauDr.EtaM.(dBeruf)Raum123  Frau Dr. Eta M. (dBeruf), Raum 123
Herr MDir. v.Raumann HerrMDir.v.Raumann ()    Herr MDir. v.Raumann, Raum
Frau Prof. von der Heiden (eBeruf) FrauProf.von der Heiden (eBeruf)    Frau Prof. von der Heiden (eBeruf), Raum
Frau Prof. Nolte-Epsilon M. (fBeruf), Raum 123 a FrauProf.Nolte-EpsilonM.(fBeruf)Raum123a Frau Prof. Nolte-Epsilon M. (fBeruf), Raum 123 a
Benannte Formeln: (Teil2)      Benannte Formeln: (Teil1)
BerufKorr: =WENN(ISTFEHLER(SUCHEN("(";$A2));WENN(ISTFEHLER(SUCHEN(",";$A2));TitelKorr&" (), ";WECHSELN(TitelKorr;",";" (),";1));WENN(ISTFEHLER(SUCHEN(",";$A2));TitelKorr&", ";TitelKorr))
NamenIso2: =TEIL(NamenIso1;1;LÄNGE(NamenIso1)-(RECHTS(NamenIso1)=".")*LÄNGE(PickOn(NamenIso1;-1)))TitelKorr: =WENN(RECHTS(PickOn($A2;2))=".";$A2;WECHSELN($A2;" ";" ";1))
NamenKorr: =WECHSELN(NamenIso2;" ";ZEICHEN(160))&WENN(RECHTS(NamenIso1)=".";"";" ")NamenIso1: =TEIL(BerufKorr;NamenAnf;SUCHEN("(";BerufKorr)-NamenAnf-1)
ZellFormeln:      NamenAnf: =FINDEN(PickOn(BerufKorr;3);BerufKorr)
C2:J2[;C3:J13]: {=VSplit(WECHSELN(WECHSELN(BerufKorr;NamenIso2;NamenKorr);",";"")&" ";;1)}L2[:L13]:=WECHSELN(VJoin(C2:G2;;-1);" ()";"")&", "&WENN(H2="";"Raum ";H2&" ")&TEXT(I2;"000"&WENN(J2="";"";" "))&J2
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Die Fmln könnten natürlich auch von einer VBA-SubPro­zedur in den Namensbereich und die Zellen geschrieben wdn (plurale Matrix­Fml beachten!) oder durch diesen folgende reine Pgm-Operationen ersetzt wdn, wobei die benannten Fmln hier als berech­nete Variab­len auftreten könnten. Aber dafür habe ich jetzt keine Zeit und du müsstest das selber machen, wenn du das Ganze unbe­dingt als Pgm haben willst (hat ja auch gewisse Vor­teile, ist idR nur nicht so pflege­leicht wie eine einmal ver­stan­dene Fml bzw die­ses System von Fmln).
Die benannten Fmln erklären ihr „Tun“ weitgehend schon durch ihren Namen. Trotzdem hier noch eine kurze Übersicht:
TitelKorr → erwartet einen Titel (erkennbar am EndPkt) als 2.TextWort; fehlt dieser, wird ein zusätzliches Leerzeichen eingefügt.
BerufKorr → verwendet die QuellText-Zelle zur Suche nach ( bzw Komma und fügt bei ihrem Fehlen () u/o Komma in den vorherigen ErgebnisText ein bzw bei Fehlen von Komma an ihn an.
NamenAnf → stellt die AnfangsPosition eines Namens im vorherigen ErgebnisText fest.
NamenIso1 → isoliert den Namen (ggf inkl Vornamen) aus dem letzten ErgebnisText.
NamenIso2 → entfernt einen evtl Vornamen (erkennbar am EndPkt) aus dem vorherigen Namens(-Teil-)Text (inkl vorangestelltem Leerzeichen; die UDF PickOn zählt hier rückwärts!).
NamenKorr → wechselt im vorherigen NamensText die Leerzeichen gg geschützte aus (ohne Wirkung, falls diese fehlen) und fügt im Falle des Fehlens eines Vornamens im 1.NamensText ersatzweise ein Leerzeichen an.
Damit sind die Vorarbeiten abgeschlossen und die splittende ZellFml muss zuvor nur noch den QuellNamen gg den kor­ri­gier­ten Namen austauschen, das Komma entfernen und 2 Leerzeichen anhängen, um bei ggf fehlenden Raum-Angaben #NV zu vermeiden.
Die Spalte für die RaumNr wurde zuvor als 000 formatiert. Diese Formatierung wird zwar von der UDF VSplit nicht explizit berück­sich­tigt, wohl aber argumen­tierungs­abhängig, ob es sich bei einem der erzeug­ten Teil­Texte um eine Zif­fern­Folge handelt. Dadurch können bei späterer Über­arbeitung keine Probleme infolge Ein­trags echter Zahlen ent­stehen.
Alles weitere wurde bereits erklärt bzw erklärt sich selbst. Die jetzt nur noch 3 benötigten UDFs wurden bereits zuvor verlinkt (in der verlinkten BspDatei mit VJoin befindet sich auch die hier ausreichende Vs1.1 von VSplit!).
Hinweis: Falls die UDFs auch noch anderweitig genutzt wdn sollen und deshalb in ein AddIn o.ä. zur persönlichen bzw allgemeinen Verwendung ausgelagert wdn, muss bei ihrer Benutzung in VBA-Proze­duren einer Mappe dort im VBE(ditor) ein Verweis auf deren Stand­ort­Datei gesetzt wdn. Das gelingt aber nur, wenn in dieser Datei die internen Standard­Namen (vor allem VBAProject!) durch spezifisch-eindeutige ersetzt wurden! Wenn sie aber nur in MappenFmln benutzt wdn sollen, ist das nicht erforderlich. Hier reicht dann die Aktivierung des AddIns (in diesem Fall kann die Mappe auch als .xlsx gespeichert wdn, nicht aber, wenn anderenfalls die UDFs in der sie anwendenden Mappe selbst gespeichert wdn!).
Hinweis für die Gegner benannter Fmln: Im Thread Formelalternative gesucht auf Office-Loesung wurde dazu ausgiebig diskutiert.
Morrn, Luc :-?


  

Betrifft: AW: So, habe mal meine FormelLösung überarbeitet, ... von: Peter
Geschrieben am: 28.09.2017 07:33:47

Hallo Luc,

besten Dank für Deine Hilfe. Aber Deine Ausführungen sind mit Sicherheit fachlich richtig, aber für einen Laienprogrammierer wie mich nicht klar dargestellt.

Ich werde daher mit den bisher fertiggestellten Ausführungen arbeiten, auch wenn diese nicht 100 %ig sind.

Nochmals vielen herzlichen Dank für Deine Hilfe.

Gruss
Peter


  

Betrifft: Was ist an meinen Ausführungen nicht ... von: Luc:-?
Geschrieben am: 28.09.2017 15:19:29

…verständlich, Peter,
und was haben die mit deinen PgmmierKenntnissen zu tun‽ Dies ist eine Lösung für Nicht-Pgmmierer! Sie müssen nur die 3 genannten und in meinem 1.Beitrag verlinkten UDFs in ein allgemeines Modul der Mappe eintragen. Alles, was ich darüber hinaus an Tipps gegeben habe, bezieht sich auf die anderweitige Nachnutzung dieser UDFs, quasi als normale Xl-Fktt.
Da du ursprünglich nur deinen VBA-Status angegeben hattest, weiß ich nicht, wie gut du in Xl bist. Bedenke aber, dass hier die Fml-Lösung, so schnell und elegant eine reine VBA-Lösung auch sein mag, praktischer ist. Man sieht Fehler auf Grund von im QuellText nicht eingehaltenen Bedingungen nicht nur sofort, sondern kann auch durch entsprd Korrektur des QuellTextes eine sofortige Ände­rung der Text­Aufteilung bewirken. Erst ganz zum Schluss, wenn alles richtig ist, sollte dieser Bereich kopiert und die Fmln mit ihren Werten über- oder die Werte in einen separaten ÄnderungsBereich (auf den sich dann auch eine ggf 2., end­gültige Rekom­bina­tions­Fml beziehen sollte) geschrieben wdn. Falls zuvor schon die RekombinationsFml eingegeben wurde, sieht man auch von Anfang an, wie das End­Ergeb­nis ausfällt. Das alles siehst du bei einem KomplettPgm immer erst zum Schluss und wirst deshalb uU mehrere Durchläufe benötigen (ganz wie unten der Thread ;-]).
Ich verstehe auch nicht, dass sich jemand mit nur geringen PgmmierKenntnissen auf Biegen und Brechen auf eine VBA-Kom­plett­Lösung versteift, die er nicht selbst erstellen kann, obwohl eine UDF-unter­stützte Fml-Lösung bereits vor­liegt. Ich glaube auch nicht, dass dich das in deiner Pgmmier­Fähigkeit wesentlich voranbringt. Dann könntest du ja auch die Pgmm der UDFs studieren → damit erreichst du beides, besseres Pgmmier- und Xl-(Fml-)Verständnis…
Im Übrigen kann jeder sehen, wie lang sich der Thread im unteren Teil heute schon hinzog, ohne dass eine der meinen adäquate Lösung erreicht wurde (evtl ja nun jetzt [erst])… ;->
Im Übrigen habe ich im vorherigen Beitrag sehr viel mehr erklärt als normalerweise und auch als hier zumeist an Erklärungen geleistet wird (eine Ausnahme bildet idR Daniel und gelegentlich auch noch der Eine oder Andere)…
Luc :-?


  

Betrifft: Letztlich ist es egal, ob sich Peter einen ... von: Daniel
Geschrieben am: 28.09.2017 16:47:51

... VBA-Code, den er nicht versteht, kopiert, ober ob er sich eine Formel kopiert, die nicht versteht.

Außerdem will der ja die getrennten Daten weiterverarbeiten, bevor er sie wieder zusammensetzt und dafür sind reine Formellösungen dann oft suboptimal.
dh ist die Aufteilung der Texte in Spalten nur temporär, dh die Formeln müssen irgendwann gelöscht und somit auch jedesmal wieder neu eingegeben werden.
Da sind deine Formeln einfach nicht praktikabel.

oder man müsste sich ein Makro schreiben, welches deine Formeln einfügt ;-)

Gruß Daniel


  

Betrifft: Letzteres hatte ich alternativ vorgeschlagen, ... von: Luc:-?
Geschrieben am: 28.09.2017 21:11:14

…Daniel,
bin aber inzwischen zum Schluss gelangt, dass Fmln günstiger sind. Abgesehen mal davon, dass ich dich hier nicht angesprochen hatte, denn du hast ja wohl inzwischen meine Analysen deinem Pgm zugrundegelegt bzw dich an ihnen orientiert, hatte ich oben auch die Gründe angeführt, die für eine Fml-Lösung sprechen, wenn's nicht gleich Hunderte von DatenSätzen wdn → der QuellText kann sofort korrigiert wdn, falls er die Grundbedingungen mal nicht einhält. Mit einem KomplettPgm ist es wie weiland in der GR-EDV mit Prüflisten, erst muss alles durchgesehen und ggf korrigiert wdn, bevor die TrennDaten in neuem Lauf erneut erstellt wdn können, es sei denn, man korrigiert gleich das TrennErgebnis, das dann aber solitär sein sollte.
Mit Fmln ist man da flexibler und kann die EndErgebnisse ggf bereinigter QuellDaten gleich auf ein neues Blatt kopieren und dort, ebenfalls per Fml, nach Überarbeitung rekombinieren. Das könnte uU sogar schneller bzw effektiver sein als ein KomplettPgm. Außerdem bleiben die QuellDaten iW erhalten.
Nenne mir nur einen ernsthaften Grund, warum er die Fmln nicht verstehen sollte, zumal ich sie auch noch erklärt habe, was ihn aber wohl nicht mehr interessiert hat (wie womöglich auch deine Endlösung)! Ich käme hier nur auf allgemeine Xl-(Fml-)Schwäche… :-]
Luc :-?


  

Betrifft: AW: Letzteres hatte ich alternativ vorgeschlagen, ... von: Daniel
Geschrieben am: 28.09.2017 22:03:23

keine Sorte Luc, deine Analysen habe ich mir nicht angeschaut, weil mir das viel zu kompliziert ist, was du da schreibst.
Außerdem habe ich meine Lösung schon 3 Tage vor dir vorgestellt, dh es war mir auch gar nicht möglich, mich mit deinen Analysen zu beschäftigen.
Ich hatte da aber nur den Code der Kernfunktion gezeigt und die vorausgehenden und nachfolgenden Arbeitschritte nur beschrieben.
Da das einfache Excelaktionen sind, die jeder Anwender auch von Hand ausführen kann und deren Ausführung sich mit Hilfe des Recorders auch relativ leicht in Makrocode umsetzen läßt, hoffte ich darauf zu verzichten zu können.
Da das Peter offensichtlich nicht dann doch nicht hinbekommen hat, habe ich dann den vollständigen Code gepostet, zumal Peter ja auch mit deiner Lösung nichts anfangen konnte (sonst hätte ich mich nicht mehr gemeldet).

das Problem mit deinen Formeln ist, dass die so komplex sind, dass auch diese Formeln nicht ohne weiteres an geänderte Datenstrukturen oder Anforderungen angepasst werden können.
Das Verständnis dieser Formeln wird auch dadurch verstärkt, dass sie neue Funktionen verwendet, die außer dir niemand kennt.


Außerdem sollten bei meiner Lösung auch mehrere Tausend Datensätze noch kein Problem darstellen und in Sekundenbruchteilen verarbeitet werden.


Achsoja, du bist ein einem Forum und das ist eine öffentliche Diskussion, in der sich jeder zu Wort melden darf.


gruß Daniel


  

Betrifft: Nun ja, du kommentierst ja nur allzugern ... von: Luc:-?
Geschrieben am: 29.09.2017 01:24:00

…auch Beiträge, die gar nicht für dich bestimmt sind, Daniel,
was die Allermeisten hier und anderswo idR nicht machen… :->
Ansonsten hattest du von deinem 1.Lösungsversuch bis gestern wesentlich länger gebraucht, das nicht dargestellte allgemeine Problem, was aber offensichtlich auftreten kann, und die grundlegende Struktur in vollem Umfang zu erkennen…
Ansonsten ist mir durchaus bewusst, dass du dich idR gern im von MS gesteckten Rahmen bewegst, aber für solche Leute schreibe ich meine UDFs, deren Konzeption und Pgmmierung wesentlich mehr Zeit gekostet haben dürfte als das, was du idR vorstellst, eben nicht. Kein Fragesteller hat bisher verlangt, die Arbeits­weise einer Xl-Fkt vollends zu verstehen (nur die kompletter Fmln; für Ersteres interessieren sich höchstens Fml-Cracks in gewissem Umfang), warum sollte dann eine fertige, universelle UDF ein derartiges Problem aufwerfen, zumal, wenn jemand ohnehin VBA einsetzen will…‽ Das ist also nur ein vorgeschobenes Schein­Argu­ment deinerseits!
Und was die Anpassungsfähigkeit an Änderungen betrifft, steht und fällt hier ohnehin alles mit der Struktur der Texte (andere Struk­turen sind bei dieser Aufgabe ohnehin irrelevant), was ich von Anfang an betont habe, ist also ebenfalls ein Schein­Argument!
Dass du meine Analyse nicht gelesen haben willst, passt durchaus zu dir. Aber meine Kommentare haben's dir offensichtlich ange­tan… :->
Es soll allerdings auch Leute geben, die nahezu alles lesen, was ich schreibe, sogar so etwas…
Übrigens bedeutet meine als Einschränkung misszuverstehende Bemerkung bzgl der DS-Anzahl nicht, dass meine Fmln nicht auch Tsde DS verarbeiten könnten, nur ist die erforderliche Handhabung im Blatt dann ggf etwas mühevoller. Aber das gilt ja für alle Fmln in dieser Dimension.
Luc :-?
PS: Für den Fall, dass Peter uns hier nochmals antworten möchte, gebe ich die Forums­Adresse deines Beitrags hier an, da er ja sonst, zumindest ohne Benach­richtigung, nicht mehr in diesem Thread antworten könnte (was er womöglich auch gar nicht vorhat):
https://www.herber.de/forum/messages/1583288.html


  

Betrifft: AW: glaubst du jetzt ernsthaft... von: Daniel
Geschrieben am: 29.09.2017 01:44:30

... ich hätte mich die ganze Zeit zwischen den beiden Beiträgen mit dem Problem beschäftigt, bis ich auf eine Lösung gekommen bin?
naja, Träum weiter.
Gute Nacht.


  

Betrifft: Nee, eben das bestimmt nicht, ... von: Luc:-?
Geschrieben am: 29.09.2017 21:08:48

…Daniel,
sondern dass du dich ursprünglich gar nicht ernsthaft mit diesem Thema auseinandergesetzt hattest, statt­dessen an seiner sicht­baren Oberfläche gehangen und das nicht richtig durchdacht und später zuerst unvoll­ständige Pgmm geliefert hast, die der Logik des ArbeitsAblaufs widersprachen. Schließlich musstest du noch ein paar mal nachbessern, obwohl dank meiner Analyse eigentlich alles klar war und nur noch in PgmForm gegossen wdn musste, wenn es denn so gewünscht war…
Luc :-?


  

Betrifft: AW: doch genau dass von: Daniel
Geschrieben am: 29.09.2017 21:25:11

Luc du irrst
ich bin nur nicht darauf gekommen, dass ich beim Text_in_Spalten doch alle Parameter setzen muss.
Thats all.
Der Rest des Makros ist so, wie ich es schon zu Anfang beschrieben habe.
Ich hatte da auf ein bisschen Eigenleistung von Peter gehofft, weil der der Rest ja doch recht trivial ist (Text in Spalten und Verkettungsfunktion mit kurzer WENN-Abfrage.)
das sollte man meiner Meinung nach auch mit geringen VBA-Kenntnissen hinbekommen.

Gruß Daniel


  

Betrifft: Du kannst ja gern glauben, was du willst, ... von: Luc:-?
Geschrieben am: 01.10.2017 01:29:42

…Daniel,
(bzw dir „erträumen“ :-]) aber nur deine SchlussBemerkung könnte ich auch so sehen, wenn da nicht ursprünglich diese merkwür­dige Lücke für EigenPgmmierung im Pgm gesteckt hätte und es dann gleich mit Verbinden weitergegangen wäre. So etwas kann leicht missverstanden wdn…
Text-in-Spalten wäre für mich niemals die 1.Wahl gewesen, weil mir dessen Ergebnis in solchen komplexen Fällen einfach zu unsi­cher u/o raumgreifend wäre, aber das ist ja nun mal dein Stil… Ich analysiere lieber, bevor ich irgendetwas unternehme (das ist mein Stil).
Du siehst ja auch hier wieder, dass Eigenleistung kaum bei Fmln zu erwarten ist, geschweige denn bei Pgmm, wenn das nicht zuvor ausdrücklich geschrieben wurde. Dazu kommt, dass viele ihre Kenntnisse überschätzen, was hier aber höchstens marginal der Fall war (der Xl-KenntnisStand wäre auch hilfreich gewesen, aber auch meine Erklärungen auf Verdacht und in beide Richtungen, wenn auch nicht völlig anfänger-ausführlich, sind letztlich nicht angekommen!).
Aber das kennen wir ja zur Genüge! Übrigens arbeite ich natürlich auch nicht ständig an derlei Themen, und ich hatte ja erwähnt, dass ich nur wenig Zeit dafür aufbringen kann, weshalb ich auch bereits Fertiges benutzt hatte. Das ist der Vorteil allgemeinerer UDFs → ich muss mir nur 'ne Fml ausdenken. Deshalb verstehe ich auch nicht, warum du so oft auf spezielle KomplettLösungen setzt… (Jetzt wohl auch Lupo!)
Aber lassen wir das; jedem Tierchen sein Plaisirchen!
Luc :-?


  

Betrifft: Du glaubst ja sowieso nur das, was du willst von: Daniel
Geschrieben am: 01.10.2017 14:03:05

Nja Luc, das TEXT IN SPALTEN wende ich ja nicht auf die komplexen Texte von Peter an, sondern auf den einfachen, den meine Function erzeugt hat und der somit ganz klar und eindeutig strukturiert ist. Somit stellt das Text-in-Spalten hier kein Problem da (wenn man es vollständig parametrisiert).

und wenn du schreibst, du musst dir "nur ne Formel ausdenken", dann ist das vielleicht richtig, aber erstens mal sind das ja dann immer noch mehrere Formeln, und die sind dann trotz Unterstützung durch "universellen" UDFs immer noch so lang und kompliziert, dass das Schreiben solcher Formeln ja schon in die Richtung Programmieren geht (eine Matrixformel ist ja im Prinzip eine For-Next-Schleife) und daher wahrscheinlich nicht weniger Zeit benötigen, als eine spezielle Makrolösung (ich hatte ja den vollständigen Code gepostet, und der ist, wie du selber sehen kannst, weder lang noch kompliziert und daher releativ schnell erstllt).

das weitere Problem speziell deiner Lösungsansätze ist ja, dass sie UDFs verwenden, deren Funktion nur dir bekannst sind und daher auch von den allerbesten Excelperten nicht verstandenen und erstellt werden können, sofern sie nicht von dir die Dokumentation und Beschreibung von deinen UDFs bekommen.

dh wenn Peter bspw deine Lösung in seiner Firma an einen anderen Mitarbeiter weitergibt, dann hat dieser keine Chance das jemals zu verstehen, es sei denn er nimmt sich die Zeit und analysiert die Codes zu deinen UDFs, und dieser Zeitaufwand ist nicht unerheblich, denn deine Codes sind komplex, nicht dokumentiert und nur sehr spärlich kommentiert.
Eigentlich bräuchte jeder, der mit deinen Lösungsvorschlägen arbeiten will, eine Zusatzqualifikation auf den Funktionsumfang deiner UDFs, und die gibst nirgendwo.

dh wenn ich deine Lösung meiner gegenüberstelle, dann sehe ich bei dir
- komplizierte Formeln, die zusätzlich noch eine VBA-Unterstüztung in Form von UDFs benötigen, die dann auch nochmal aufwendig programmiert sind
und bei mir nur ein einfaches, kurzes Makro und eine einfache Function.

Gruß Daniel


  

Betrifft: Deine Zielstellung ist halt eine andere als die … von: Luc:-?
Geschrieben am: 03.10.2017 01:08:41

…meine, Daniel,
denn meine Erfahrung mit Xl, mitunter auch im Ggsatz zu anderer diesbzgl Software, hat zu mitunter komplexen UDFs geführt (im Archiv sind idR nur solche der meinen zu finden, die weniger als 100 CodeZeilen haben), was mich letztlich auch viele Xl-Fktt besser verstehen ließ (ich meine natürlich ihren pgmtechnischen Hintergrund und ihr Zusammenwirken mit Xl in Fmln). Das dürfte man nur mit Nutzung der von Xl vorgegebenen Möglichkeiten und von VBA im von MS anscheinend vorgedachten Umfang nicht erreichen.
Jeder kann natürlich für sich selbst entscheiden, wieviel er von dem allen verstehen will, wobei wohl die meisten bzgl VBA auf die reine SubRoutinen-Pgmmierung verfallen dürften (mit Fktt nur als Hilfsmittel). Ich dagg versuche da fortzusetzen, wo MS bei den Xl-Fktt für viele Jahre aufgehört hatte und viele Lücken bis heute nicht geschlossen hat.
Du wurdest ja schon von anderer Seite mal gefragt, was du denn dagg hast, dass ich solche UDFs kosten­los anbiete! Das ist dann doch im Grunde genommen so, wie mit (neuen) Xl-Fktt, zu denen idR auch keine Dokumentation vorliegt, die alle Möglichkeiten erklärt. Eine solche (inkl Bspp) macht nämlich noch mal nahezu doppelt soviel Arbeit wie die Fkt selbst. Ich weiß das, denn für ca 5 Dtzd hatte ich das mal gemacht. Und schließl kann jeder mit meinen UDFs genauso wie mit Xl-Fktt experi­mentieren, um ihre Möglichkeiten auszuloten…
Bei dieser Aufgabe hier waren meine Fmln nicht kompliziert, nur aufgeteilt. Da wirst auch du wohl schon wesentlich kompliziertere bei einigen Fml-Cracks gesehen haben, was auch einige deiner Fml-Lösungen nicht unbedingt ausschließt… :-]
Übrigens, wenn ich Functions nach dem BaukastenPrinzip aufbaue, kann ich sie kaum noch in einem Forum posten, es sei denn, ein ganzes AddIn. Und das ginge mir dann doch etwas zu weit (abgesehen mal davon, dass bisher kaum jemand so etwas macht)…
Luc :-?


  

Betrifft: AW: Traurig... von: Peter
Geschrieben am: 29.09.2017 09:05:59

Hallo Daniel,

...dass ihr euch hier im Forum über die Methode streitet.

Erst nachdem ich Deine letzte Hilfe bezüglich dem Makro verarbeitet hatte, habe ich die Beiträge
gelesen, die ihr euch - Luc und Du - geliefert habt.


Es wird immer mehrere Lösungen für ein Problem geben. Das gibt es im ganzen Leben und auch bei Excel.

Es ist auch fast egal, ob ich eine Formel oder Code zu 100 % verstehe. Wichtig ist erst einmal ob das
Produkt das ihr mir freundlicherweise erarbeitet, bei mir funktioniert. Wenn dies der Fall ist, dann
beschäftige ich mich damit, wie ich danach das erarbeitete ggf. umbauen kann für wieder andere Probleme.

Wie auch immer, ich bedanke mich bei Dir und hoffe auch in Zukunft eure Hilfe zu erhalten. Das ist das wunderbare hier in diesem Forum.

Gruss
Peter


  

Betrifft: AW: Traurig... von: Peter
Geschrieben am: 29.09.2017 09:03:05

Hallo Luc,

dass ihr euch hier im Forum über die Methode streitet.

Es wird immer mehrere Lösungen für ein Problem geben. Das gibt es im ganzen Leben und auch bei Excel.

Es ist auch fast egal, ob ich eine Formel oder Code zu 100 % verstehe. Wichtig ist erst einmal ob das
Produkt das ihr mir freundlicherweise erarbeitet, bei mir funktioniert. Wenn dies der Fall ist, dann
beschäftige ich mich damit, wie ich danach das erarbeitete ggf. umbauen kann für wieder andere Probleme.

Wenn Du - Luc - so nett gewesen wärst, in meine übermittelte Datei Deine Arbeit eingefügt hättest, dann
hätte dies mit Sicherheit funktioniert.

Wie auch immer, ich bedanke mich bei Dir und hoffe auch in Zukunft eure Hilfe zu erhalten. Das ist das wunderbare hier in diesem Forum.

Gruss
Peter


  

Betrifft: Es geht nur scheinbar um die Methode, ... von: Luc:-?
Geschrieben am: 29.09.2017 21:48:48

…Peter,
das Problem sitzt tiefer, nämlich einerseits in Daniels Angewohnheit, auch Beiträge zu kommentieren, die gar nicht für ihn bestimmt sind, hier einen, auf den ich eine AW von dir erwartet hätte, nicht von ihm, und andererseits um die unterschwellige Abwertung der Arbeit anderer, die oft in seinen Beiträgen mitschwingt. Weil er nicht dazu bereit ist, strategisch zu denken, sondern lieber von Pro­blem zu Problem taktiert, soll Letzteres auch das Non-Plus-Ultra sein…
Ansonsten hatte mich deine 1.AW schon enttäuscht, nicht weil du die Fmln nicht nutzen willst, sondern weil du das Ganze nicht recht verstanden haben willst, was mich vermuten ließ, dass es auch um deine Xl-Kenntnisse nicht so gut bestellt ist. Es kann natürlich sein, dass du 2 Fliegen mit einer Klappe schlagen und zugleich deine PgmmierKenntnisse verbessern willst, aber ob diese Aufgabe sich dafür gut eignet, wage ich doch zu bezweifeln. Ginge es dir in 1.Linie um die Lösung des Problems, hättest du die Lösung bereits mit meinem 2.Beitrag gehabt…
Nun schreibst du aber, ich hätte dir das in deine (letzte?) Datei einbauen sollen. Hat das denn Daniel gemacht…‽ Was ich davon gesehen hatte, entsprach doch meiner Vorgabe! Also komme ich erneut zu der Schlussfolgerung, dass deine Xl-Kenntnisse so gering sind, dass du eine ausgiebig erläuterte FmlLösung nicht adäquat übernehmen kannst. Definierte Namen sollte man schon kennen und auch, wie sie zu handhaben sind, und das hat mit Pgmmierung erst mal rein gar nichts zu tun…
Und da du dich (evtl deshalb!) als Pgmmierer versuchst, solltest du eigentlich wissen, was man mit einer nutzerdefinierten Fkt (UDF) anstellen muss, damit sie genauso wie eine Xl-Fkt in ZellFmln eingesetzt wdn kann. Hier hatte ich dann wohl des Guten zuviel getan und dir prognostisch ein paar Hinweise gegeben, die dein Verständnis überforderten… (Da musst du nachholen!)
Auf jeden Fall solltest du deine Xl-Kenntnisse verbessern, sonst dürftest du zukünftig auch Probleme mit dir angebotenen (Stan­dard-)Fml-Lösungen bekommen, denn hier sind auch etliche Fml-Cracks zugange…
Auf jeden Fall waren deine beiden letzten AWen ziemlich enttäuschend und du kannst sicher sein, dass ich mir nicht noch einmal, hier trotz Zeitmangels, soviel Mühe (mit dir) machen werde. Und dann ant­wortet auch noch dieser arrogante Besserwisser, der sich schon mit einigen AWern angelegt hat, an deiner Stelle… :-[
Luc :-?


  

Betrifft: AW: es geht hier nicht um die Methode, von: Daniel
Geschrieben am: 29.09.2017 22:24:37

sondern darum, das Luc dein Problem kilometerweit am Arsch vorbei geht und hier nicht unterwegs ist, um anderen zu helfen, sondern um sich zu präsentieren und zu zeigen wie toll er Programmieren und formulierern kann.
Deswegen schreibt er auch gerne in diesem pseudowissenschaftlichen Stil mit den vielen Abkürzungen, damit auch gleich alle sehen können, in welchen hochwissenschaftlichen Kreisen er früher mal unterwegs war (zumindest behauptet er sowas gelegentlich). Ihm ehts hier nicht darum, anderen zu helfen, sondern darum, seine UDFs, die er irgendwann mal geschrieben hat zu präsentieren und um den intelektuellen "Schwanzvergleich" (um es mal salopp zu formulieren), irgendwie glaubt er, sich mit oftmals unnötiger komplexität beweisen zu müssen und hat dann ein Problem, wenn jemand in der Lage ist, die gestellte Aufgabe auf eine deutlich einfachere Weise zu lösen als er es versucht hat. Dann versucht er, diese Lösungen abzuwerten, indem er sie als "nicht strategisch" oder "nicht universell genug" (eine seiner Lieblingsvokabeln für diesen Zweck, ohne aber genauer darauf einzugehen, was das überhaupt bedeuten soll, also eine klassische Totschlargumentation)

Gruß Daniel


  

Betrifft: Und dir geht's offensichtlich in 1.Linie ... von: Luc:-?
Geschrieben am: 01.10.2017 02:34:18

…tatsächlich nur um Diffamierung und Abwertung der Leistung anderer, wie ja verschiedentlich schon festgestellt wdn konnte. Damit hast du dir hier unter den AWern keine Freunde gemacht, was dir offen­sichtlich am Allerwertesten vorbeigeht (um nicht in deine GossenSprache abzurutschen). An deiner Ausdrucksweise kann man jedenfalls gut erkennen, was dir wichtig zu sein scheint! :->
Hier (und zT auch anderswo) kannst du wohl nur (kurzfristige) Freunde unter den FrageStellern gewinnen…
Die Motivation, die du mir unterstellst, trifft ja wohl eher auf dich zu, was man auch daran erkennen kann, dass du nur allzugern Streit anfängst, womit du bei den Meisten hier regelmäßig abblitzt…
Meine letzte AW an Peter mag dich ja geärgert haben, aber das wäre dann ja nur ein gerechter Ausgleich… :->
Wer sich stets mit simplen Lösungen von (auch komplexen) Problemen zufriedengibt, wird nie das wirklich Einfache erreichen, denn der Weg dorthin führt oftmals über das Komplizierte. Simples mag in 90% aller Fälle ausreichen, aber das ist idR nur für absolute Anfänger eine Offenbarung.
Mit deiner Sichtweise spielst du jedenfalls in einer ganz eigenen Liga (um einen alten Vgl aufzu­grei­fen), die ggf nur noch von Leuten bevölkert wird, die mit MSO-Anfänger-Schulungen ihr Geld verdienen. Apropos Geld, hast du nicht einst geschrieben, dass du komplexe Pgmm nur gg Bares schreiben würdest‽ Das ist zwar dein gutes Recht, aber uneigennützig wäre das ja nun gerade nicht… :->
Und letztlich wollen alle, die hier antworten, einerseits helfen und andererseits meist auch selber noch dazulernen und natürlich auch ihre Lösungen vorstellen. Das ist letztlich auch der Sinn eines Forums → jeder soll profitieren! Warum also wirfst du mir genau das vor! Nur, weil du selber schon alles zu wissen glaubst und was darüber hinausgeht, dich nicht interessiert‽ Sicher muss man sich auch irgendwann selbst beschränken, weil niemand alles können kann, aber dann soll man auch nicht einen solchen Eindruck erwecken wollen. Trotzdem hat jeder ein Recht, auf seine Leistung stolz zu sein.
Übrigens, was ich hier in nunmehr 12 Jahren angeboten habe, ist nur die sprichwörtliche Spitze des Eisbergs. Es gibt ein Mehrfaches, was ich nicht in ein Forum gesetzt habe bzw stellen würde, und Einige hier wissen das auch. Auch du hast ca 10 Jahre Zeit gehabt, Ähnliches zu bewerkstelligen und hast es anscheinend nicht getan. Warum verleumdest* du also jemanden, der es getan hat‽ Das sieht doch irgendwie nach purer Missgunst aus…
Und was das FormulierenKönnen betrifft, scheinst du nie einen Chef gehabt zu haben, der in fast jedem Satz eines von dir verfassten Schriftstücks rumkorrigiert hat. Ich schon! Hier aber habe ich bestimmte Schreibweisen aus diversen Gründen zu meinem (unverwechselbaren) MarkenZeichen erkoren. Wenn du übliche Prosa von mir lesen willst, musst du in einer anderen Art von Forum tätig wdn (dort gibt's dann noch ein paar sauber Formulierende mehr, wenn auch leider nicht die Mehrheit).
* Dir ist schon bewusst, dass dein vorheriger BT den juristischen StraftatBestand Verleumdung und üble Nachrede erfüllt…‽
Luc :-?


  

Betrifft: AW: Und dir geht's offensichtlich in 1.Linie ... von: Daniel
Geschrieben am: 01.10.2017 13:15:41

Hi Luc

nein, mir geht es nicht darum, deine Leistung zu diffamieren, aber es ist einfach eine Tatsache, dass dein Lösung von Peter nicht verstanden wurde.
Was auch daran liegen könnte, dass du die Aufgabenstellung, die Peter zu bewältigen hat, noch nicht verstanden hast (damit meine ich nicht das gestellte Problem von Text aufteilen und wieder zusammensetzen, sondern sein Gesamtprojekt).

und was den Eisberg angeht, mein lieber Luc, da kennst du tatsächlich nur die Spitze.
Den Rest kennst du nicht und auch niemand im Forum, weil ich darüber im Forum einfach nicht rede.
Nur weil du es es nicht kennst, brauchst du nicht zu glauben das es sowas nicht gibt.
Gruß Daniel


  

Betrifft: Dann hättest du dich auch nicht derart ... von: Luc:-?
Geschrieben am: 03.10.2017 01:58:58

…artikulieren sollen, Daniel,
weil das nämlich normalerweise genauso verstanden wird!
Warum Peter meine umfangreichen Erläuterungen nicht verstanden hat, wird wohl einerseits daran liegen, dass auch seine Xl-Kenntnisse nicht umfänglich genug sind und er andererseits auf eine reine SubRoutinen-Lösung selbstfixiert war, so dass er nicht erkannt hatte, dass es sich bei meiner Lösung um eine reine Fml-Lösung mit VBA-Anteil in Form von UDFs gehandelt hat. Das hatte ich aber bereits zur Genüge aus­ge­führt…
Aber woran willst du erkannt haben, dass ich sein primäres Problem nicht verstanden hätte? Darüber hat er 1. nichts geschrieben und 2. war wohl offensichtlich, dass es um Ergänzung und Korrektur von Texten einer bestimmten Art ging, die er einfacher gestal­ten wollte. Und das kann man auf 2erlei Art bewerk­stelligen, mit kompletten Pgmm oder mit Fmln (Letzteres ist oft sehr kompliziert, aber mit meinen UDFs über­schaubar, was ich bewiesen habe).
Tja, Eisberge; deiner scheint sich ja wohl komplett unter der WasserOberfläche zu befinden, etwas, was man mal meinte, auch meinem vorwerfen zu können, weshalb ich irgend­wann damit anfing, „Spitze“ zu zeigen… Und natürlich habe ich wenig Interesse daran, nur zum Zeit­Ver­treib Weg­werf-Pgmm zu schreiben. Solche gelegent­lichen (Übungs-)Varianten behalte ich lieber für mich…
Übrigens bietet HWH auf seinen inzwischen freigegebenen Seiten auch eine Vielzahl von Prozeduren an (mehr als ich vor 12 Jahren hatte!). Würdest du auch kritisieren, wenn mal jemand darauf ver­weisen würde? Meine UDFs sind aber lt Forums­Bedin­gungen nun quasi auch in sein Eigen­tum über­gegan­gen. Folg­lich ist ein entsprd Archiv­Verweis gleich­bedeu­tend mit einem Verweis auf HWHs Pro­ze­duren zu sehen, nur mit dem Unter­schied, dass der Autor ein anderer ist und ich damit in der Ver­gangen­heit kein Geld ver­dient hatte. So sind sie allen­falls Wer­bung für meine Produkte, die aber zZ niemand kaufen kann. Ganz ähnlich ist das auch mit Ver­weisen auf excel­formeln u.a. WebSites. Dort steht auch oft der Autor dabei. Also, was soll's, nur, weil ich meist selber ver­weisen muss (tue ich das mal nicht, gibt's auch wieder Kritik)…‽
Du kennst doch sicher das Sprichwort, allen recht getan ist eine Kunst, die niemand kann
Luc :-?


  

Betrifft: Für evtl Nachnutzer von: Luc:-?
Geschrieben am: 01.10.2017 00:55:25

Musste leider feststellen, dass ich die Argumentierung 2er UDFs gleich gesetzt hatte, obwohl sie das nicht ist. Meine unver­öffent­lichte UDF Pick und auch die eine o.a. kann rückwärts zählen, bei PickOn bedeutet ein negatives 2.Argument aber das Ausnehmen dieser von vorne gezählten Position aus dem Ganzen. Im Bsp hatte das nicht zu Fehlern geführt, könnte es aber in der Realität…
Deshalb muss hier noch eine 4.UDF eingeführt wdn (wenn man lange und umständliche TeilFmln vermeiden will) → CountOn (Vs1.1). Die benannte Fml NamenIso2 müsste dann in angepasster LangVersion so lauten:
NamenIso2: =TEIL(NamenIso1;1;LÄNGE(NamenIso1)-(RECHTS(NamenIso1)=".")*(LÄNGE(PickOn(NamenIso1;CountOn(NamenIso1;" ")+1))+1))
Da PickOn ohnehin eingesetzt wdn muss, kann man aber auch auf die Xl-Fkt TEIL verzichten, was eine kürzere Fml ergäbe:
NamenIso2: =PickOn(NamenIso1;ZEILE(INDIREKT("1:"&CountOn(NamenIso1;" ")+1-(RECHTS(NamenIso1)="."))))
(Im TabBlatt wäre das eine singulare MatrixFml, benannt aber nicht!)
Es geht aber auch noch kürzer, wenn man das oben Geschilderte ausnutzt und nur eine evtl vorhandene Vornamen­Abkürzung weglässt:
NamenIso2: =GLÄTTEN(PickOn(NamenIso1;-(CountOn(NamenIso1;" ")+1)*(RECHTS(NamenIso1)=".")))
Viel Erfolg! Luc :-?


  

Betrifft: Natürlich zZ noch offen! owT von: Luc:-?
Geschrieben am: 27.09.2017 01:26:09

:-?


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Daniel
Geschrieben am: 27.09.2017 14:47:10

Hi Peter

damit du mal zu deiner Lösung kommst, probiere mal diesen Code:

er trennt die Daten auf und fügt sie hinterher wieder zusammen, dh in den Zwischenteil kannst du dann deinen Code zum umarbeiten der Liste einfügen.

Sub TextAufteilenUndWiederZusammenfügen()
Dim FO As String
'--- Text aufteilen
With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
    With .Offset(0, 2)
        .FormulaR1C1 = "=SauberTrennen(RC1)"
        .Formula = .Value
        .TextToColumns _
            Destination:=.Cells(1, 1), _
            DataType:=xlDelimited, _
            Semicolon:=True, _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
                             Array(5, 1), Array(6, 1), Array(7, 2), Array(8, 1))
    End With
End With

'--- hier kannst du jetzt deinen Code löschen, sortieren usw einfügen

    MsgBox "hier dann der weiter Code zum löschen und sortieren"

'--- Text wieder zusammenfügen
FO = FO & "=SUBSTITUTE(TRIM(RC[2]&"" ""&RC[3]&"" ""&RC[4]&"" ""&RC[5]&"" ""&RC[6]&"
FO = FO & "IF(COUNTA(RC[7]:RC[9])>0,"", ""&RC[7]&"" ""&RC[8]&"" ""&RC[9],"""")),"" ,"","","")"

With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
    .FormulaR1C1 = FO
    .Formula = .Value
    .Offset(0, 2).Resize(, 8).ClearContents
End With

End Sub
der Code benötigt als Unterfunktion die Funktion, die ich dir schon gezeigt habe, dh diese, welche du dir ebenfalls in ein Modul kopieren musst.
Function SauberTrennen(txt As String) As String

Dim Erg(0 To 7) As String
Dim TT() As String
Dim T As Long

TT = Split(Replace(txt, ",", " ,"), " ")

'--- Anrede
Erg(0) = TT(0)

'--- Titel
If TT(1) = "Dr." Then
    Erg(1) = TT(1)
    T = 2
Else
    T = 1
End If

'--- Name
Erg(2) = TT(T)

'--- Vorname
For T = T + 1 To UBound(TT)
    If TT(T) = "," Or TT(T) Like "(*" Or TT(T) = "Raum" Then Exit For
    If Erg(3) = "" Then
        Erg(3) = TT(T)
    Else
        If TT(T) <> "" Then Erg(3) = Erg(3) & " " & TT(T)
    End If
Next

'--- Beruf
For T = 0 To UBound(TT)
    If TT(T) Like "(*" Then
        Erg(4) = TT(T)
        Exit For
    End If
Next

'--- Raum
For T = 0 To UBound(TT)
    If TT(T) = "Raum" Then
        Erg(5) = TT(T)
        If T + 1 <= UBound(TT) Then Erg(6) = TT(T + 1)
        If T + 2 <= UBound(TT) Then Erg(7) = TT(T + 2)
    End If
Next

SauberTrennen = Join(Erg, ";")
End Function
ist getestet mit deiner Beispieldatei.
Bei Namen, die aus mehr als zwei Wörtern bestehen, wird der erste Name der ersten Zelle zugeordnet und alle folgenden Namen der zweiten Zelle. der Stopper hierfür ist die Klammer (Beruf) oder das Komma.

Gruß Daniel


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 27.09.2017 16:20:47

Hallo Daniel,

ich habe den Code in Modul eingefügt - funktioniert nicht ganz.

Die Daten, welche ich in Spalte A eingefügt habe:

Herr Dr. Alpha, Raum 101
Herr Beta
Frau Gamma, Raum 006
Herr Delta (aBeruf), Raum 112 a
Frau Dr. Epsilon (bBeruf), Raum 123
Herr Vau M., Raum 101
Herr Dr. Zeta, Raum 103
Herr Vau N. (cBeruf), Raum 112 a
Frau Dr. Eta M. (dBeruf), Raum 123
Herr MDir. v.Raumann
Frau Prof. `von der Heiden´ (eBeruf)
Frau Prof. von der Heiden (eBeruf), Raum 123 a
Frau Prof. Nolte-Epsilon M. (fBeruf), Raum 123 a


Die Daten nach korrektur:

Herr Dr. Alpha Raum 101
Herr Beta
Frau Gamma Raum 006
Herr Delta (aBeruf) Raum 112 a
Frau Dr. Epsilon (bBeruf) Raum 123
Herr Vau M. Raum 101
Herr Dr. Zeta Raum 103
Herr Vau N. (cBeruf) Raum 112 a
Frau Dr. Eta M. (dBeruf) Raum 123
Herr MDir. v.Raumann
Frau Prof. `von der Heiden´ (eBeruf)
Frau Prof. von der Heiden (eBeruf) Raum 123 a
Frau Prof. Nolte-Epsilon M. (fBeruf) Raum 123 a


Das Ergebnis, welches ich erhalte:

Herr1 Dr. Alpha, Raum 101
Herr1 Beta
Frau1 Gamma, Raum 006
Herr1 Delta (aBeruf), Raum 112 a
Frau1 Dr. Epsilon (bBeruf), Raum 123
Herr1 Vau M., Raum 101
Herr1 Dr. Zeta, Raum 103
Herr1 Vau N. (cBeruf), Raum 112 a
Frau1 Dr. Eta M. (dBeruf), Raum 123
Herr1 MDir. v.Raumann
Frau1 Prof. `von der, Heiden´ (eBeruf)
Frau1 Prof. von der, Heiden (eBeruf) Raum
Frau1 Prof. Nolte-Epsilon M., (fBeruf) Raum 123

Die Daten, welche fehlen:












123 a
a

Vielleicht kannst Du das bitte noch korregieren.

Besten Dank.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 27.09.2017 16:23:46

Hallo Daniel,
vorherige Daten nicht richtig angegeben.
ich habe den Code in Modul eingefügt - funktioniert nicht ganz.

Die Daten, welche ich in Spalte A eingefügt habe:

Herr Dr. Alpha, Raum 101
Herr Beta
Frau Gamma, Raum 006
Herr Delta (aBeruf), Raum 112 a
Frau Dr. Epsilon (bBeruf), Raum 123
Herr Vau M., Raum 101
Herr Dr. Zeta, Raum 103
Herr Vau N. (cBeruf), Raum 112 a
Frau Dr. Eta M. (dBeruf), Raum 123
Herr MDir. v.Raumann
Frau Prof. `von der Heiden´ (eBeruf)
Frau Prof. von der Heiden (eBeruf), Raum 123 a
Frau Prof. Nolte-Epsilon M. (fBeruf), Raum 123 a


Die Daten nach korrektur:

Herr1 Dr. Alpha Raum 101
Herr1 Beta
Frau1 Gamma Raum 006
Herr1 Delta (aBeruf) Raum 112 a
Frau1 Dr. Epsilon (bBeruf) Raum 123
Herr1 Vau M. Raum 101
Herr1 Dr. Zeta Raum 103
Herr1 Vau N. (cBeruf) Raum 112 a
Frau1 Dr. Eta M. (dBeruf) Raum 123
Herr1 MDir. v.Raumann
Frau1 Prof. `von der Heiden´ (eBeruf)
Frau1 Prof. von der Heiden (eBeruf) Raum 123 a
Frau1 Prof. Nolte-Epsilon M. (fBeruf) Raum 123 a



Das Ergebnis, welches ich erhalte:

Herr1 Dr. Alpha, Raum 101
Herr1 Beta
Frau1 Gamma, Raum 006
Herr1 Delta (aBeruf), Raum 112 a
Frau1 Dr. Epsilon (bBeruf), Raum 123
Herr1 Vau M., Raum 101
Herr1 Dr. Zeta, Raum 103
Herr1 Vau N. (cBeruf), Raum 112 a
Frau1 Dr. Eta M. (dBeruf), Raum 123
Herr1 MDir. v.Raumann
Frau1 Prof. `von der, Heiden´ (eBeruf)
Frau1 Prof. von der, Heiden (eBeruf) Raum
Frau1 Prof. Nolte-Epsilon M., (fBeruf) Raum 123

Die Daten, welche fehlen:












123 a
a

Vielleicht kannst Du das bitte noch korregieren.

Besten Dank.

Gruss
Peter


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Daniel
Geschrieben am: 27.09.2017 16:48:02

kannst du da mal genauer beschreiben, was fehlt (Beispieldatei hochladen)
welche Korrekturen hast du gemacht?
ich könnte mir das zwar vielleicht auch selber raussuchen, aber da du mich nicht bezahlst, denke ich, das es angemessen ist, dass du mir soviel Arbeit abnimmst wie möglich
btw, ist "Prof" ein Name oder ein Titel?
welche Titel können noch vorkommen, ich brauche da die vollständige Liste, weil es ja sonst kein sauberes Kriterium gibt, ob das zweite Wort der Titel oder ein Name ist.


Gruß Daniel


  

Betrifft: AW: Zellen trennen wieder zusammenfügen von: Peter
Geschrieben am: 27.09.2017 16:55:38

Hallo Daniel,

anbei die Datei: https://www.herber.de/bbs/user/116589.xlsm

Gruss
Peter


  

Betrifft: Deine Korrektur verschiebt die Spalten von: Daniel
Geschrieben am: 27.09.2017 17:53:46

Hi
schau dir die hochgelandene Datei nochmal genau an.
Mein Marko nutzt nur die Spalten C-J, aber die angeblich fehlenden Texte stehen in den Spalten K und L.
Das bedeutet, dass du in deiner Korrketurbearbeitung hier die Spalten nach rechts verschoben hast.
damit kann die Zusammenfassungsformel natürlich nicht korrket arbeiten, weil sie davon ausgeht, dass die Texte in den Spalten stehen, in die sie die Trennfunktion geschrieben hat und die darfst du nicht verändern.

dh du müsstst da deine Korrektur überarbeiten, mein Code kann da nicht viel machen.

die von dir spezifizerten Spalten sind:
1. Anrede
2. Titel
3. Name
4. Vorname
5. Beruf
6. "Raum"
7. Raumnummer
8. Raumzusatz

diese müssen so eingehalten werden und du darfst sie nachträglich nicht ändern.


bzw, hier nochmal die Trennfunktion überarbeitet:
du kannst jetzt auch mehrere Titel angeben (Dr. Prof. MDr., weitere können ergänzt werden)
und Nachnamen, die in Anführungszeichen stehen, (`von der Heiden´), werden auch bei mehreren Wörtern in eine Zelle geschrieben.
Vielleicht erleichtert dir das ja die korrektur der Daten

Function SauberTrennen(txt As String) As String
 
 Dim Erg(0 To 7) As String
 Dim TT() As String
 Dim T As Long
 
 
 TT = Split(Replace(txt, ",", " ,"), " ")
 
 '--- Anrede
 Erg(0) = TT(0)
 
 '--- Titel
 Select Case TT(1)
    Case "Dr.", "Prof.", "MDir."
        Erg(1) = TT(1)
        T = 2
    Case Else
        T = 1
 End Select
 
 '--- Name, wenn Anf. Zeichen, namen zusammenfassen
 If TT(T) Like "[`´""']*" Then
    For T = T To UBound(TT)
        If Erg(2) = "" Then
            Erg(2) = TT(T)
        Else
            Erg(2) = Erg(2) & " " & TT(T)
        End If
        If TT(T) Like "*[`´""']" Then Exit For
    Next
Else
    Erg(2) = TT(T)
End If
 
 '--- Vorname
 For T = T + 1 To UBound(TT)
     If TT(T) = "," Or TT(T) Like "(*" Or TT(T) = "Raum" Then Exit For
     If Erg(3) = "" Then
         Erg(3) = TT(T)
     Else
         If TT(T) <> "" Then Erg(3) = Erg(3) & " " & TT(T)
     End If
 Next
 
 '--- Beruf
 For T = 0 To UBound(TT)
     If TT(T) Like "(*" Then
         Erg(4) = TT(T)
         Exit For
     End If
 Next
 
 '--- Raum
 For T = 0 To UBound(TT)
     If TT(T) = "Raum" Then
         Erg(5) = TT(T)
         If T + 1 <= UBound(TT) Then Erg(6) = TT(T + 1)
         If T + 2 <= UBound(TT) Then Erg(7) = TT(T + 2)
     End If
 Next
 
 SauberTrennen = Join(Erg, ";")
 End Function
gruß Daniel


  

Betrifft: AW: Deine Korrektur verschiebt die Spalten von: Peter
Geschrieben am: 28.09.2017 07:29:57

Hallo Daniel,

besten Dank für Deine Hilfe.

Es sind noch zwei Bereich, welche nicht so funktionieren, wie evtl. benötigt, aber für meine Zwecke
genügt es. Bei Nachnamen, die in Anführungszeichen stehen, (`von der Heiden´), werden auch bei mehreren Wörtern in eine Zelle geschrieben. Das funktioniert, ist aber in der Regel nicht vorhanden.

Wenn dann müssten schon Nachnamen, die nicht in Anführungszeichen (von der Heiden) in eine Zelle erfasst werden. Das dürfte aber sicherlich ein Problem sein.

Ich werde mich daher, da es für meine Zwecke ausreicht mit der jetzigen Ausführung zufrieden geben.

Nochmals herzlichen Dank für Deine Hilfe.

Gruss

Peter


  

Betrifft: AW: Deine Korrektur verschiebt die Spalten von: Daniel
Geschrieben am: 28.09.2017 08:12:53

Hi

Die Frage ist, woran ich erkennen kann, wo der Nachname endet und wo der Vorname beginnt, dh ob
"Peter Franz Marc" jetzt zu "Peter-Franz Marc" oder zu "Peter Franz-Marc" werden soll.

Wenn's dafür ne Regel gibt, sag sie mir, dann kann ich sie einbauen.
Oder es gilt die Regel, dass Vornamen grundsätzlich nur mit Buchstabe+Punkt abgekürzt werden. Wenn das so ist, könnte man das auch in die Abfrage einbauen.

Gruß Daniel


  

Betrifft: AW: Deine Korrektur verschiebt die Spalten von: Peter
Geschrieben am: 28.09.2017 08:46:35

Hallo Daniel,
danke, dass Du Dich nochmals meldest und Interesse hast mir zu helfen:

Ich habe eine Liste erstellt, links das Orginal wie dies sein muss mit den Varianten.
Rechts dazu die Varianten, wenn es sich um z. B. von der ... handelt.

Herr Dr. Alpha, Raum 101 Herr Dr. von der Alpha, Raum 101
Herr Beta, Herr von der Beta,
Frau Gamma, Raum 006 Frau von der Gamma, Raum 006
Herr Delta (aBeruf), Raum 112 a Herr von der Delta (aBeruf), Raum 123 a
Frau Dr. Epsilon (bBeruf), Raum 123 Frau Dr. von der Epsilon (bBeruf), Raum 123
Herr Vau M., Raum 101 Herr von der Vau M., Raum 101
Herr Dr. Zeta, Raum 103 Herr Dr. von der Zeta, Raum 103
Herr Vau N. (cBeruf), Raum 112 a Herr von der Vau N. (cBeruf), Raum 123
Frau Dr. Eta M. (dBeruf), Raum 123 Frau Dr. von der Eta M. (dBeruf), Raum 123
Herr MDir. v.Raumann, Herr MDir. v. Raumann,
Frau Prof. `von der Heiden´ (eBeruf), Frau Prof. `von der Heiden´ (eBeruf), Raum 123
Frau Prof. von der Heiden (eBeruf), Raum 123 Frau Prof. von der Heiden (eBeruf), Raum 123 a
Frau Prof. Nolte-Epsilon M. (fBeruf), Raum 123 a Frau Prof. Nolte-Epsilon M. (gBeruf), Raum 123 a
Frau Prof. Dr. Nolte-Epsilon M. (gBeruf), Raum 123a Frau Prof. Dr. Nolte-Epsilon M. (gBeruf), Raum 123 a
Herr Prof. Dr. Vau N. (cBeruf), Raum 112 Herr Prof. Dr. Vau N. (cBeruf), Raum 112

Du schreibst, dass man erkennen muss wo der Name anfängt bzw endet.

1. = . Name,
2. = Herr Name,
3. = Frau Name,
4. = Herr Name (
5. = . Name (
6. = Herr Name M. (statt M *)
7. = . Name,
8. = Herr Name M. (statt M *)
9. = . Name M. (statt M *)
10. = . Name,
11. = . Name (
12. = . Name (
13. = . Name M. (statt M *)
14. = 2ter . Name M. (statt M *)
15. = 2ter . Name N. (statt N *)

Ich hoffe das nützt Dir etwas als Anhaltspunkt, falls nicht dann eben nicht.

Nochmals besten Dank für Deine Hilfe.

Gruss
Peter


  

Betrifft: AW:Rückfrage von: Daniel
Geschrieben am: 28.09.2017 09:48:00

Hi
kannst du bitte klar definieren, was Name und was Vorname ist?
Beispiele sind ja ganz schön, aber eine klare Beschreibung, die keine Beispiele benötigt, ist besser.

aus diesen Beispielen geht jetzt hervor, dass der Vorname entweder gar nicht oder wenn, dann nur in der Form "Buchstabe+Punkt" angegeben wird.
Ist das richtig? Wenn ja, könnte man die Funktion natürlich auch darauf auslegen.
Aber das müsstest du bitte nach durchsicht der Realdaten bestätigen.

deine Auflistung von 15 Beispielen hilft mir jetzt auch nicht weiter.
Was soll ich daraus ableiten?
Kann es auch vorkommen, dass die Anrede fehlt?

Gruß Daniel


  

Betrifft: AW: AW:Rückfrage von: Peter
Geschrieben am: 28.09.2017 11:17:43

Hallo Daniel,

das Programm kommt grundsätzlich ohne Vornamen aus. Jedoch, wenn mind. 2 Personen mit dem selben Namen vorkommen benötigt man als Unterscheidung die Abkürzung des Vornamens mit Punkt.

Die Anrede fehlt grundsätzlich nicht.

Ich habe den Bedarf geändert, weil Luc auf div. Vorkommnisse reagiert hatte. welche ich nicht berück- sichtigt hatte.

Ich hoffe, dass diese Daten verständlich sind.

Gruss
Peter


  

Betrifft: AW: AW:Rückfrage von: Daniel
Geschrieben am: 28.09.2017 11:44:12

HI

dann probier mal diese Funktion:
hier sind jetzt folgende Punkte berücksichtigt:
- ein Titel liegt vor, wenn das zweite Wort mindestens zwei Buchstaben hat und mit einem Punkt endet (Dr., Prof., MDr., aber nicht v.)
- das erste Wort nach Anrede/Titel ist immer Nachname
- alle nachfolgenden Wörter die nicht mit Punkt enden, werden dem Nachnamen zugeordnet
- ein auf Punkt endendes Wort wird dem Vornamen zugeordnet, es ist ein Vorname zulässig

Function SauberTrennen(txt As String) As String

Dim Erg(0 To 7) As String
Dim TT() As String
Dim T As Long

TT = Split(Replace(txt, ",", " ,"), " ")

'--- Anrede
Erg(0) = TT(0)

'--- Titel
If TT(1) Like "*??." Then
    Erg(1) = TT(1)
    T = 2
Else
    T = 1
End If

'--- Name / Vornae
Erg(2) = TT(T)
For T = T + 1 To UBound(TT)
    If TT(T) = "," Or TT(T) Like "(*" Or TT(T) = "Raum" Then Exit For
    If TT(T) Like "*." Then
        Erg(3) = TT(T)
        Exit For
    End If
    Erg(2) = Erg(2) & " " & TT(T)
Next


'--- Beruf
For T = 0 To UBound(TT)
    If TT(T) Like "(*" Then
        Erg(4) = TT(T)
        Exit For
    End If
Next

'--- Raum
For T = 0 To UBound(TT)
    If TT(T) = "Raum" Then
        Erg(5) = TT(T)
        If T + 1 <= UBound(TT) Then Erg(6) = TT(T + 1)
        If T + 2 <= UBound(TT) Then Erg(7) = TT(T + 2)
    End If
Next

SauberTrennen = Join(Erg, ";")
End Function
Gruß Daniel


  

Betrifft: AW: AW:Rückfrage von: Peter
Geschrieben am: 28.09.2017 11:59:11

Hallo Daniel,

besten Dank für Dein Makro.

Es sind noch kleine Fehler vorhanden:

in Zeile 12: Frau Prof. `von der Heiden´, (eBeruf) - Komma falsch - gehört nach )
in Zeile 13: Frau Prof. von der Heiden, (eBeruf) Raum -Komma falsch - gehört nach ) und RaumNr 123
in Spalte K fehlt
in Spalte 15: Frau Prof. Dr. Nolte-Epsilon M., (gBeruf) Raum 123a - Komma falsch - gehört nach )
in Spalte 16: Herr Prof. Dr. Vau N., (cBeruf) Raum 112 - Komma falsch - gehört nach )

Ansonsten perfekt!
Ich nehme an, dass diese Fehler von Dir korregierbar sind.

Besten Dank

Gruss
Peter


  

Betrifft: AW: AW:Rückfrage von: Daniel
Geschrieben am: 28.09.2017 12:20:33

kann ich ohne Datei nicht prüfen.
ich bin auch zu faul, mir das alles wieder aus deinen Beiträgen zusammenzusetzen.
lade mal bitte die Datei mit den Daten und dem Makro hoch.
die nicht überarbeiteten Ausgangsdaten sollten wieder in Spalte M oder weiter rechts stehen.

Gruß Daniel


  

Betrifft: AW: AW:Rückfrage von: Peter
Geschrieben am: 28.09.2017 13:54:52

Hallo Daniel,

anbei die gewünschte Datei.
https://www.herber.de/bbs/user/116606.xlsm
Gruss
Peter


  

Betrifft: AW: Lösung von: Daniel
Geschrieben am: 28.09.2017 14:23:22

ok, der Fehler ist, dass im TextToColumns die Parameter nicht vollständig gesetzt werden und dann die Einstellungen aus alten Vorgängen übernommen werden, was dann zu veränderten Ergebnissen führt.
(die Veränderungen liegen im TextToColumns)

der Code für das Makro muss so aussehen (erster Teil bis zum auftrennen):

Sub TextAufteilenUndWiederZusammenfügen()
Dim FO As String
'--- Text aufteilen
With Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
    With .Offset(0, 2)
        .FormulaR1C1 = "=SauberTrennen(RC1)"
        .Formula = .Value
        .TextToColumns _
            Destination:=.Cells(1, 1), _
            DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, _
            Semicolon:=True, _
            Space:=False, _
            Tab:=False, _
            Comma:=False, _
            other:=False, _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
                             Array(5, 1), Array(6, 1), Array(7, 2), Array(8, 1))
    End With
End With
Das Zusammenfassen und die Funktion bleiben wie gehabt.

Gruß Daniel


  

Betrifft: AW: Lösung von: Peter
Geschrieben am: 29.09.2017 08:48:14

Hallo Daniel,

Super, super, super! Es klappt einwandfrei.

Vielen Dank für Deine Mühe und der Geduld, die Du mit mir aufbringen musstest.

Gruss
Peter


Beiträge aus den Excel-Beispielen zum Thema "Zellen trennen wieder zusammenfügen"