Transforming Data Table

Li Chen,

if you can use Power Query then this would be the (not optimized) code.

let
 Source = Excel.CurrentWorkbook(){[Name="tbl_Data"]}[Content],
 #"Replaced Value" = Table.ReplaceValue(Source," ","#",Replacer.ReplaceText,{"Txt"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Txt", type text}, {"NN", type text}}),
 #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "NN", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"NN.1", "NN.2", "NN.3"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"NN.1", Int64.Type}, {"NN.2", Int64.Type}, {"NN.3", Int64.Type}}),
 #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
 #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Txt", "Index"}, "Attribut", "Wert"),
 #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribut"}),
 #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "de-DE")[Index]), "Index", "Txt"),
 #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Zusammengeführt"),
 #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Zusammengeführt", Text.Trim, type text}}),
 #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Zusammengeführt", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Zusammengeführt.1", "Zusammengeführt.2", "Zusammengeführt.3"}),
 #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Zusammengeführt.1", type text}, {"Zusammengeführt.2", type text}, {"Zusammengeführt.3", type text}}),
 #"Replaced Value1" = Table.ReplaceValue(#"Changed Type2","#"," ",Replacer.ReplaceText,{"Zusammengeführt.1", "Zusammengeführt.2", "Zusammengeführt.3"}),
 #"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Zusammengeführt.1", "1"}, {"Zusammengeführt.2", "2"}, {"Zusammengeführt.3", "3"}, {"Wert", "NN"}})
in
 #"Renamed Columns"