[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.

 

[Rick Rothstein]

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

Is the above example a true representation of your data... can there be more than one set of numbers (highlighted in red) that need to be ignored? Your first example had one such number but the above example has two... is there some rule governing how many of them there could be? If there could be more, does that only apply to the first value you want to retrieve or could there be more than one for the other values after it as well?

 

[mzafar1076]

Rick,

Really appreciate it.

Good catch...

I made a mistake, so you are right....take a look at this string instead:

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

Thanks.

 

[Rick Rothstein]

Good catch...

I needed to catch it as it affects whether a formula solution exists or not.:wink:

Assuming your first value is in cell AC1, put this formula in cell AD1 and copy it across for as many cells as you think you will ever have output for, then copy all of those cells down to the bottom of your data...

=TRIM(MID(SUBSTITUTE(";"&$AC1,";",REPT(" ",300)),(2*COLUMNS($AD:AD)-1)*300,300))

Note that this formula assumes your data in any one cell will never be longer than 300 characters. If it could be, then change all of the 300 to that length, but be warned, the larger you make those numbers, the less efficient the formula will become and the less fields it will be able to break out.

 

[sandy666]

are you able to use PowerQuery (Get&Transform)?

 

[mzafar1076]

Fantastic....it works for the most part....

Here is a small issue...

When I try it on one of the strings, here is the output I get....anyway to get rid of "#"

RECG0485429 #RECG0293969 #RECG0261579 #RECG1805163 #RECG2054797

 

[mzafar1076]

I am sure, I can.

 

[sandy666]

source
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]POLABCD0001043;#8920;#RECG0293969;#7054;#RECG0261579;#7011;#RECG1805163;#8392;#RECG2054797;#8547;RECG2435542;#8834;#RECG 2435699;#8835;#RECG2208113;#8716;#RECG1846076;#8424;#RECG2033072;#8535[/td][/tr]

[tr=bgcolor:#FFFFFF][td]POLABCD0001043;#8920;#7232;#RECG0293969;#7054;#RECG0261579;#7011;#RECG1805163;#8392;#RECG2054797;#8547;RECG2435542;#8834 ;#RECG2435699;#8835;#RECG2208113;#8716;#RECG1846076;#8424;#RECG2033072;#8535[/td][/tr]
[/table]

result
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG0293969[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG0261579[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG1805163[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG2054797[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG2435542[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG 2435699[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG2208113[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG1846076[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG2033072[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG0293969[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG0261579[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG1805163[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG2054797[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG2435542[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG2435699[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG2208113[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG1846076[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RECG2033072[/td][/tr]
[/table]

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"})
in
    #"Replaced Value"

 

[mzafar1076]

This is fantastic Sandy and thank you, but is there any way to put these values in rows instead of columns?

 

[Rick Rothstein]

Fantastic....it works for the most part....

Here is a small issue...

When I try it on one of the strings, here is the output I get....anyway to get rid of "#"

RECG0485429 #RECG0293969 #RECG0261579 #RECG1805163 #RECG2054797

Try this...

=TRIM(MID(SUBSTITUTE(";"&SUBSTITUTE($AC1,"#",""),";",REPT(" ",300)),(2*COLUMNS($AD:AD)-1)*300,300))