[Comma Delimited String] Need Help Extract Values

[mzafar1076]

I have tried several formulas from this forum, but I cannot get what I need.

I have a long spreadsheet with over 1000 values....

One of the columns....columns AC contains the following string....this is just an example...

These strings are not fixed.

There could only be one value or several values.

Example of one value:

POLABCD0001043;#8920;

Several Value Example String:

POLABCD0001043;#8920;#7232;#RECG0293969;#7054;#RECG0261579;#7011;#RECG1805163;#8392;#RECG2054797;#8547;RECG2435542;#8834;#RECG2435699;#8835;#RECG2208113;#8716;#RECG1846076;#8424;#RECG2033072;#8535

From the string listed above, here is what I am trying to get, but in rows instead of columns.....so, column AD would get the first value, AE, AF and so on...

POLABCD0001043
RECG0293969
RECG0261579
RECG1805163
RECG2054797
RECG2435542;
RECG2435699
RECG1846076
RECG2033072

Is there someone that might know how can I accomplish this?

Thank you in advance.

 

[sandy666]

you mean like this?

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

Column11

Column12

Column13

Column14

Column15

Column16

Column17

Column18

RECG0293969

RECG0261579

RECG1805163

RECG2054797

RECG2435542

RECG 2435699

RECG2208113

RECG1846076

RECG2033072

RECG0293969

RECG0261579

RECG1805163

RECG2054797

RECG2435542

RECG2435699

RECG2208113

RECG1846076

RECG2033072

or

result

RECG0293969, RECG0261579, RECG1805163, RECG2054797, RECG2435542, RECG 2435699, RECG2208113, RECG1846076, RECG2033072, RECG0293969, RECG0261579, RECG1805163, RECG2054797, RECG2435542, RECG2435699, RECG2208113, RECG1846076, RECG2033072

 

[mzafar1076]

Thank you Rick.

That does it....

RECG0485429 RECG0293969 RECG0261579 RECG1805163 RECG2054797

I appreciate it.

 

[mzafar1076]

yes.

 

[sandy666]

for the second option from post#11

M 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(";", 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}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Text.Contains([Column1], "REC")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","#","",Replacer.ReplaceText,{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"result")
in
    #"Merged Columns"

but maybe easier for you will be use Rick's solution