[Comma Delimited String] Need Help Extract Values

mzafar1076

New Member
Joined
Sep 22, 2017
Messages
8
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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?
 
Last edited:

mzafar1076

New Member
Joined
Sep 22, 2017
Messages
8
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,780
are you able to use PowerQuery (Get&Transform)?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,780
source
Column1
POLABCD0001043;#8920;#RECG0293969;#7054;#RECG0261579;#7011;#RECG1805163;#8392;#RECG2054797;#8547;RECG2435542;#8834;#RECG 2435699;#8835;#RECG2208113;#8716;#RECG1846076;#8424;#RECG2033072;#8535
POLABCD0001043;#8920;#7232;#RECG0293969;#7054;#RECG0261579;#7011;#RECG1805163;#8392;#RECG2054797;#8547;RECG2435542;#8834 ;#RECG2435699;#8835;#RECG2208113;#8716;#RECG1846076;#8424;#RECG2033072;#8535

result
Column1
RECG0293969
RECG0261579
RECG1805163
RECG2054797
RECG2435542
RECG 2435699
RECG2208113
RECG1846076
RECG2033072
RECG0293969
RECG0261579
RECG1805163
RECG2054797
RECG2435542
RECG2435699
RECG2208113
RECG1846076
RECG2033072

M code
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

New Member
Joined
Sep 22, 2017
Messages
8
This is fantastic Sandy and thank you, but is there any way to put these values in rows instead of columns?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows

Forum statistics

Threads
1,081,832
Messages
5,361,586
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top