3 wiederkehrende werte aus langer zeile trennen und in neue spalten

[PeterN]

Hola los Todos!

Ich habe eine Liste mit folgendem Inhalt pro ZEILE:

Code:

<option label="Bulgarisch" value="number:1">Bulgarisch</option><option label="Dänisch" value="number:4">Dänisch</option><option label="Deutsch" value="number:10" selected="selected">Deutsch</option><option label="Englisch" value="number:6">Englisch</option><option label="Estnisch" value="number:7">Estnisch</option><option label="Finnisch" value="number:8">Finnisch</option><option label="Französisch" value="number:9">Französisch</option><option label="Griechisch" value="number:11">Griechisch</option><option label="Irisch" value="number:14">Irisch</option><option label="Isländisch" value="number:13">Isländisch</option><option label="Italienisch" value="number:15">Italienisch</option><option label="Kroatisch" value="number:2">Kroatisch</option><option label="Lettisch" value="number:16">Lettisch</option><option label="Litauisch" value="number:17">Litauisch</option><option label="Maltesisch" value="number:18">Maltesisch</option><option label="Niederländisch" value="number:5">Niederländisch</option><option label="Norwegisch" value="number:19">Norwegisch</option><option label="Polnisch" value="number:20">Polnisch</option><option label="Portugiesisch" value="number:21">Portugiesisch</option><option label="Rumänisch" value="number:22">Rumänisch</option><option label="Schwedisch" value="number:26">Schwedisch</option><option label="Slowakisch" value="number:23">Slowakisch</option><option label="Slowenisch" value="number:24">Slowenisch</option><option label="Spanisch / Kastilisch" value="number:25">Spanisch / Kastilisch</option><option label="Tschechisch" value="number:3">Tschechisch</option><option label="Ungarisch" value="number:12">Ungarisch</option>

Die Struktur ist immer:

Code:

<option label=" WERT1" value="number:WERT2">WERT3</option>

Ich bräuchte eine Lösung, die diese drei WERTE in die Spalten C, D und E schreibt.

Also in A1 steht die lange Zeile, in B1 die Formel, in C1 WERT1, in D1 WERT2 und in E1 WERT3. Der Rest kann weg.

Oder anders gesagt

C1 D1 E1
Bulgarisch 1 Bulgarisch
Dänisch 4 Dänisch
Deutsch 10 Deutsch
usw.

Danke und Gruss

Peter

[maninweb]

Hallo,

keine Ahnung, ob ich jetzt 100 Prozent verstanden habe, was Du möchtest - anbei jedoch eine benutzerdefinierte VBA-Funktion,
die Dir je nach Parametern die Werte in eine Zelle schreibt. Textumbruch für die Zielzellen einschalten.

Code:

A1 = Der XML Text
C1 = XMLSplitter(A1;"label")
D1 = XMLSplitter(A1;"value")
E1 = XMLSplitter(A1)

Code:

Public Function XMLSplitter(Source As Range, Optional Key As String) As String
  
  Dim d() As String
  Dim r   As String
  Dim n   As Long

  r = ""
  d = Split(Replace(Source.Cells(1, 1).Value, "><", ">$<"), "$")
  
  Select Case Key
    
    Case "label"
      
      For n = LBound(d, 1) To UBound(d, 1)
        
        r = r & Split(Split(d(n), Key & "=""")(1), """")(0)
        r = r & IIf(n < UBound(d, 1), vbCrLf, "")
        
      Next
      
    Case "value"
      
      For n = LBound(d, 1) To UBound(d, 1)
        
        r = r & Split(Split(Split(d(n), Key & "=""")(1), """")(0), ":")(1)
        r = r & IIf(n < UBound(d, 1), vbCrLf, "")
        
      Next
      
    Case Else
      
      For n = LBound(d, 1) To UBound(d, 1)
        
        r = r & Split(Split(d(n), """>")(1), "</")(0)
        r = r & IIf(n < UBound(d, 1), vbCrLf, "")
        
      Next
      
  End Select
  
  XMLSplitter = r
  
End Function

Gruß

[shift-del]

Moin

Also WERT1 und WERT3 sind immer identisch? Dann kann man sich ja WERT3 ersparen.

Lösung in M-Code (Power Query):

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter("</option>", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type1", "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "=", " "), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters.1", each Text.BetweenDelimiters([Column1], ":", ">"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Between Delimiters1",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Text Between Delimiters] <> null and [Text Between Delimiters] <> "")
in
    #"Filtered Rows"

[PeterN]

Moin Jungs!

Herzlichen Dank für die Lösungen.

Wie füge ich die Codes denn in die Excelliste ein, damit ich es nutzen kann?

Oder würdet Ihr mir den Code bitte in die Beispielliste einbauen? (s.Anhang)

P.S. WERT1 und WERT3 sind FAST immer gleich (Bei der Beispielliste schon, aber leider NICHT immer).

Gruss

Peter

[maninweb]

Hallo,

für die VBA-Lösung.

Gruß

[Fennek]

Hallo,

wenn man den gegeben Text am Anfang um und am Ende um ergänzt, dann geht:

Code:

[xml]$XML = get-content C:\Users\User\Desktop\iXML.xml
#$xml.FirstChild.InnerText
for ($i=0;$i -le $xml.ChildNodes.option.count; $i++) {
    $xml.ChildNodes.option[$i].InnerText
}

mfg

(Powershell)

[PeterN]

Hi Mourad!

Danke für die Liste.

Mit meiner Beispielzeile funktioniert es prima.

Nur wenn ich A1 durch eine andere Zeile wie meine Beispielzeile zum trennen austausche passiert nichts

Wie kann ich den Code denn mit einer anderen Liste starten/nutzen?

Gruss

Peter

---

Hi Fennek!

Danke für den Code. 

Für welchen der beiden vorgeschlagenen Lösungen ist der denn gedacht?

Gruss

Peter

[Fennek]

dieser Code liest alle 3 Werte aus:

Code:

[xml]$XML = get-content C:\Users\xxxxx\Desktop\iXML.xml # anpassen

for ($i=0;$i -le $xml.ChildNodes.option.count-1; $i++) {
    
    $label = $xml.ChildNodes.option[$i].label
    $val = $xml.ChildNodes.option[$i].value.Split(":")[1]
    $inTx = $xml.ChildNodes.option[$i].InnerText
    write-host ($label, $val,$inTx)
}

[snb]

Code:

Sub M_snb()
   Cells(1).Replace Left(Cells(1), 15), ""
   Cells(1).Replace """ value=""number:", "|"
   Cells(1).Replace "</option>", "|"
   Cells(1).Replace """>", "|"
   Cells(1).Replace """ selected=""selected", ""
   Cells(1).Replace " ", ""
   Cells(1).TextToColumns , , , , 0, 0, 0, 0, -1, "|"
End Sub

[PeterN]

Danke an alle!

Also aktuell habe ich jetzt vier offene Baustellen:

snb = Deinen Code habe ich zum Laufen gekriegt, aber er trennt alles in eine Zeile. Also nebeneinander und ich bräuchte die getrennten 3-er Blöcke untereinander in Spalte C, D und E.

fennek = Ich verstehe leider nicht wie und wo ich Deinen Code zum Laufen kriegen soll. Soll ich die Datei umbenennen und auf dem Desktop speichern? Kannst Du mir bitte eine Liste schicken wo das läuft?

shift-del = Wie kriege ich M-Code (Power Query) zum Laufen? Kannst Du mir bitte eine Liste schicken wo das läuft?

manimweb = Mit meiner Beispielzeile funktioniert es prima. Nur wenn ich A1 durch eine andere Zeile wie meine Beispielzeile zum trennen austausche passiert nichts. Wie kann ich den Code denn mit einer anderen Liste starten/nutzen?

Danke

Peter