Extract Text between Two Different Characters

[merlinCLT]

Good morning from Charlotte, NC!

I feel like there will be a relatively simple solution here; however, I spent the better part of the morning yesterday trying to construct the appropriate formula to extract data from a cell and could not successfully accomplish the task. Basically, I have survey responses from a Likert scale matrix that have been aggregated into one cell. I need to have that data separated and included in individual columns representing the response of each respondent for each survey item. Here is what I have:

RESPONSES​	S1​	S2​	S3​	S4​
S1: Strongly Agree, S2: Agree, S3: Agree, S4: Strongly Agree
S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: Agree
S1: Agree, S2: Disagree, S3: Agree, S4: Agree
S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: Agree
S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: Strongly Agree
S1: Agree, S2: Agree, S3: Strongly Agree, S4: Strongly Agree
S1: Agree, S2: Agree, S3: Agree, S4: Agree
S1: Agree, S2: Agree, S3: Strongly Agree, S4: Strongly Agree
S1: Agree, S2: Agree, S3: Agree, S4: Agree

​

Here is what I want to achieve:

RESPONSES​	S1​	S2​	S3​	S4​
S1: Strongly Agree, S2: Agree, S3: Agree, S4: Strongly Agree	Strongly Agree	Agree	Agree	Strongly Agree
S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: Agree	Strongly Agree	Strongly Agree	Strongly Agree	Agree
S1: Agree, S2: Disagree, S3: Agree, S4: Agree	Agree	Disagree	Agree	Agree
S1: Strongly Agree, S3: Strongly Agree, S4: Agree	Strongly Agree		Strongly Agree	Agree
S1: Strongly Agree, S2: Strongly Agree	Strongly Agree	Strongly Agree
S3: Strongly Agree, S4: Strongly Agree			Strongly Agree	Strongly Agree
S1: Agree, S2: Agree, S3: Agree, S4: Agree	Agree	Agree	Agree	Agree
S1: Agree, S2: Agree, S3: Strongly Agree, S4: Strongly Agree	Agree	Agree	Strongly Agree	Strongly Agree
S1: Agree, S2: Agree, S3: Agree, S4: Agree	Agree	Agree	Agree	Agree

I think I figured out a formula for column S1 [=MID(H2,SEARCH("S1:",H2)+4,SEARCH(",",H2)-SEARCH("S1:",H2)-4)], but a having a hard time figuring out the LEN or MID and RIGHT functions. Thanks, in advance, for any thoughts or solutions!

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	RESPONSES	S1	S2	S3	S4
2	S1: Strongly Agree, S2: Agree, S3: Agree, S4: Strongly Agree	Strongly Agree	Agree	Agree	Strongly Agree
3	S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: Agree	Strongly Agree	Strongly Agree	Strongly Agree	Agree
4	S1: Agree, S2: Disagree, S3: Agree, S4: Agree	Agree	Disagree	Agree	Agree
5	S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: Agree	Strongly Agree	Strongly Agree	Strongly Agree	Agree
6	S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: Strongly Agree	Strongly Agree	Strongly Agree	Strongly Agree	Strongly Agree
7	S1: Agree, S2: Agree, S3: Strongly Agree, S4: Strongly Agree	Agree	Agree	Strongly Agree	Strongly Agree
8	S1: Agree, S2: Agree, S3: Agree, S4: Agree	Agree	Agree	Agree	Agree
9	S1: Agree, S2: Agree, S3: Strongly Agree, S4: Strongly Agree	Agree	Agree	Strongly Agree	Strongly Agree
10	S1: Agree, S2: Agree, S3: Agree, S4: Agree	Agree	Agree	Agree	Agree
Reports

Cell Formulas
Range	Formula
B2:E10	B2	=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,": ","<x>"),",", "</x></m><m>")&"</x></m></k>","//m[contains(.,'"&B$1&"')]/x")

 

[merlinCLT]

@Fluff: Thanks so much! This is spot on! I was not previously familiar with FILTERXML. I will have to look for some online resources/videos to learn more about the function. If you have any recommendations, I would appreciate the direction. Thanks, again, for the solution!

 

[Fluff]

You're welcome & thanks for the feedback.
I have no idea what there is on the net regarding the function, so cannot recommend anything.

 

[alansidman]

Here is an alternative solution employing Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "RESPONSES", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"RESPONSES.1", "RESPONSES.2", "RESPONSES.3", "RESPONSES.4"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter1", {"Value.1"}, {{"Data", each _, type table [Attribute=text, Value.1=nullable text, Value.2=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Attribute", "Value.2", "Index"}, {"Custom.Attribute", "Custom.Value.2", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data", "Custom.Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value.1]), "Value.1", "Custom.Value.2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.Index"})
in
    #"Removed Columns1"