Determine the number of search results upon using different sets of separators

[Iemand]

Dear,

I would like some help on the formulation of a formula in Excel in order to compare the total number of search results upon using different sets of separator characters.

As I have multiple columns with content, as in the example below, I thought it would be possible to Count the search results in some way and do this for each column separately ( I would actually prefer to treat each column separately).
Column A
1 L-516-S-221-S-223
2 H-140.STR3
3 ST0 XP 23-9

Preferably, I would like to use a varying a set of separator characters in order to determine the impact on the number of search results based on this set of separator characters. Logically, with an increasing number of separators more results will be returned (depending on separators included in the cell values of course).

The set of characters that I would like to experiment with is: “-_ .,;: “

Hopefully this makes sense and someone is able to help me out.

Kind regards,

 

[oldbrewer]

Re: Determine the number of search results upon using different sets of separators (Excel) - Please help

what if you had this

2 H-140.STR3-fred;sid tom . harry.A99

note there is a blank either side of the dot between tom and harry

 

[Iemand]

Re: Determine the number of search results upon using different sets of separators (Excel) - Please help

what if you had this

2 H-140.STR3-fred;sid tom . harry.A99

note there is a blank either side of the dot between tom and harry

If all of this would be in one cell it should also return '.' as a single result since I included the 'space' in the list of separators mentioned in my original message.

Looking forward to your answer to my question.

Thanks.

 

[oldbrewer]

Re: Determine the number of search results upon using different sets of separators (Excel) - Please help

	-	.		,	_	:	;		1	2	3	4	5	6
1 L-516-S-221-S-223	6	0	2	0	0	0	0		1		L	-	5	1
2 H-140.STR3	2	2	2	0	0	0	0		2		H	-	1	4
3 ST0 XP 23-9	2	0	4	0	0	0	0		3		S	T	0
bob.fred-sid tom harry	2	2	3	0	0	0	0		b	o	b	.	f	r
bob,sid_harry	0	0	0	2	2	0	0		b	o	b	,	s	i
bob:sid:harry;sue;pat	0	0	0	0	0	3	1		b	o	b	:	s	i
	columns B to H calculate the sector count for each type of separator
	is this what you want

<colgroup><col><col span="14"></colgroup><tbody>
</tbody>

 

[Iemand]

Re: Determine the number of search results upon using different sets of separators (Excel) - Please help

	-	.		,	_	:	;		1	2	3	4	5	6
1 L-516-S-221-S-223	6	0	2	0	0	0	0		1		L	-	5	1
2 H-140.STR3	2	2	2	0	0	0	0		2		H	-	1	4
3 ST0 XP 23-9	2	0	4	0	0	0	0		3		S	T	0
bob.fred-sid tom harry	2	2	3	0	0	0	0		b	o	b	.	f	r
bob,sid_harry	0	0	0	2	2	0	0		b	o	b	,	s	i
bob:sid:harry;sue;pat	0	0	0	0	0	3	1		b	o	b	:	s	i
	columns B to H calculate the sector count for each type of separator
	is this what you want

<tbody>
</tbody>

Dear,

This is exactly what I am looking for. The number of strings returned when using a certain separator. I can simply add the ones in order to calculate the total of strings for a certain set of separators. What is the Excel code that I need to put in the cel? Thank you

 

[Iemand]

Re: Determine the number of search results upon using different sets of separators (Excel) - Please help

Would you have time to share the Excel code?
Thanks

 

[oldbrewer]

Re: Determine the number of search results upon using different sets of separators (Excel) - Please help

you are using countif on the range of individual characters

generic code =countif(B2:T2,a2)+1

so if a2 = "," you count all the commas in the range B2:T2 and add 1 to the total