Extract Text between Two Different Characters

merlinCLT

New Member
Joined
Mar 3, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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 AgreeAgreeAgreeStrongly Agree
S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: Agree
Strongly AgreeStrongly AgreeStrongly AgreeAgree
S1: Agree, S2: Disagree, S3: Agree, S4: Agree
AgreeDisagreeAgreeAgree
S1: Strongly Agree, S3: Strongly Agree, S4: Agree
Strongly AgreeStrongly AgreeAgree
S1: Strongly Agree, S2: Strongly Agree
Strongly AgreeStrongly Agree
S3: Strongly Agree, S4: Strongly Agree
Strongly AgreeStrongly Agree
S1: Agree, S2: Agree, S3: Agree, S4: Agree
AgreeAgreeAgreeAgree
S1: Agree, S2: Agree, S3: Strongly Agree, S4: Strongly Agree
AgreeAgreeStrongly AgreeStrongly Agree

S1: Agree, S2: Agree, S3: Agree, S4: Agree
AgreeAgreeAgreeAgree

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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
How about
+Fluff 1.xlsm
ABCDE
1RESPONSESS1S2S3S4
2S1: Strongly Agree, S2: Agree, S3: Agree, S4: Strongly AgreeStrongly AgreeAgreeAgreeStrongly Agree
3S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: AgreeStrongly AgreeStrongly AgreeStrongly AgreeAgree
4S1: Agree, S2: Disagree, S3: Agree, S4: AgreeAgreeDisagreeAgreeAgree
5S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: AgreeStrongly AgreeStrongly AgreeStrongly AgreeAgree
6S1: Strongly Agree, S2: Strongly Agree, S3: Strongly Agree, S4: Strongly AgreeStrongly AgreeStrongly AgreeStrongly AgreeStrongly Agree
7S1: Agree, S2: Agree, S3: Strongly Agree, S4: Strongly AgreeAgreeAgreeStrongly AgreeStrongly Agree
8S1: Agree, S2: Agree, S3: Agree, S4: AgreeAgreeAgreeAgreeAgree
9S1: Agree, S2: Agree, S3: Strongly Agree, S4: Strongly AgreeAgreeAgreeStrongly AgreeStrongly Agree
10S1: Agree, S2: Agree, S3: Agree, S4: AgreeAgreeAgreeAgreeAgree
Reports
Cell Formulas
RangeFormula
B2:E10B2=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2,": ","<x>"),",", "</x></m><m>")&"</x></m></k>","//m[contains(.,'"&B$1&"')]/x")
 
Upvote 0
@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!
 
Upvote 0
You're welcome & thanks for the feedback.
I have no idea what there is on the net regarding the function, so cannot recommend anything.
 
Upvote 0
Here is an alternative solution employing Power Query.

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"
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top