Separating numbers from a string of text

[Anune153]

Hi all!

I am pretty new to going beyond just the basics within excel. Currently, I am trying to clean data that is pretty simple but I want to automate the process as much as possible. Basically, participants from a survey were given a list of statements numbered 1-27 and then asked to list which statements they agreed with the most by listing the statement number. My first column looks like this: "1,22,3,5". Or some people didn't put commas so it may look like this: "1 22 3 5". Or a mix.

I want to be able to count how many times participants mentioned each statement, sort of like a count if, and place that total count on another cell rather than manually having to tally it myself. I am having difficulty I guess because I can't use the count if function with strings...

Any help is much appreciated, I have had difficulty explaining which is probably why my searches have come up empty. Thank you!

 

[JoeMo]

You could use Data>Data Tools>Text to Columns to convert either of the two string formats in your examples to individual numbers, each in a separate column..

 

[FormR]

Hi, here is another option you could try.

Book1
	A	B	C	D
1
2			Statement	Count
3	1 22 3 5		1	5
4	1,22,4,5		2	1
5	1,22 3,5		3	4
6	1, 3 , 6 22 4,5		4	3
7	1 2 3 , 4, 5,6, 7		5	5
8			6	2
9			22	4
Sheet1

Cell Formulas
Range	Formula
D3:D9	D3	=SUMPRODUCT(0+ISNUMBER(FIND(" "&C3&" "," "&TRIM(SUBSTITUTE($A$3:$A$100,","," "))&" ")))

 

[Anune153]

Hi, here is another option you could try.

Book1
	A	B	C	D
1
2			Statement	Count
3	1 22 3 5		1	5
4	1,22,4,5		2	1
5	1,22 3,5		3	4
6	1, 3 , 6 22 4,5		4	3
7	1 2 3 , 4, 5,6, 7		5	5
8			6	2
9			22	4
Sheet1

Cell Formulas
Range	Formula
D3:D9	D3	=SUMPRODUCT(0+ISNUMBER(FIND(" "&C3&" "," "&TRIM(SUBSTITUTE($A$3:$A$100,","," "))&" ")))

Thank you very much!

 

[sandy666]

just for fun as an option...
with XL2016/Windows and Power Query

Raw		Raw	Count
1 22 3 5		1	5
1,22,4,5		2	1
1,22 3,5		3	4
1, 3 , 6 22 4,5		4	3
1 2 3 , 4, 5,6, 7		5	5
		6	2
		7	1
		22	4

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Raw", Splitter.SplitTextByAnyDelimiter({",","."," "}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Raw"),
    Clean = Table.TransformColumns(Table.SelectRows(Split, each ([Raw] <> "")),{{"Raw", Text.Trim, type text}}),
    Group = Table.Group(Clean, {"Raw"}, {{"Count", each Table.RowCount(_), type number}}),
    PadStart = Table.AddColumn(Group, "Custom", each Text.PadStart([Raw], 2, "0")),
    RC = Table.RemoveColumns(Table.Sort(PadStart,{{"Custom", Order.Ascending}}),{"Custom"})
in
    RC