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

I

Iemand

New Member
Joined
Jun 23, 2017
Messages
4
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,
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
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


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

oldbrewer said:
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
Click to expand...


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.
 
Upvote 0
Re: Determine the number of search results upon using different sets of separators (Excel) - Please help:)

[TABLE="width: 1076"]
<colgroup><col><col span="14"></colgroup><tbody>[TR]
[TD][/TD]
[TD]-[/TD]
[TD].[/TD]
[TD] [/TD]
[TD],[/TD]
[TD]_[/TD]
[TD]:[/TD]
[TD];[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]1 L-516-S-221-S-223[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]L[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2 H-140.STR3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]H[/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3 ST0 XP 23-9[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]3[/TD]
[TD] [/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bob.fred-sid tom harry[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]o[/TD]
[TD]b[/TD]
[TD].[/TD]
[TD]f[/TD]
[TD]r[/TD]
[/TR]
[TR]
[TD]bob,sid_harry[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]o[/TD]
[TD]b[/TD]
[TD],[/TD]
[TD]s[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TD]bob:sid:harry;sue;pat[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]o[/TD]
[TD]b[/TD]
[TD]:[/TD]
[TD]s[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]columns B to H calculate the sector count for each type of separator[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is this what you want[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Determine the number of search results upon using different sets of separators (Excel) - Please help:)

oldbrewer said:
[TABLE="width: 1076"]
<tbody>[TR]
[TD][/TD]
[TD]-[/TD]
[TD].[/TD]
[TD][/TD]
[TD],[/TD]
[TD]_[/TD]
[TD]:[/TD]
[TD];[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]1 L-516-S-221-S-223[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]L[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2 H-140.STR3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]H[/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3 ST0 XP 23-9[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bob.fred-sid tom harry[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]o[/TD]
[TD]b[/TD]
[TD].[/TD]
[TD]f[/TD]
[TD]r[/TD]
[/TR]
[TR]
[TD]bob,sid_harry[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]o[/TD]
[TD]b[/TD]
[TD],[/TD]
[TD]s[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TD]bob:sid:harry;sue;pat[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]o[/TD]
[TD]b[/TD]
[TD]:[/TD]
[TD]s[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]columns B to H calculate the sector count for each type of separator[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is this what you want[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Return and rank top 3 results (including ties) from VSTACK, FILTER, CHOOSECOLS, and SORT formula
Replies
8
Views
206
Peter_SSs
Peter_SSs
H
  • Solved
Calculation With Numbers Separated By Hyphen
Replies
9
Views
319
honkin
H
J
  • Solved
Using Index/Match (or Something Else) to Return Row(s) of Data
Replies
4
Views
167
jbennett01
J
S
  • Question Question
Excel Formula to build out Combinations/Permutations of a series of numbers
Replies
1
Views
218
Eric W
Eric W
Keebellah
  • Question Question
Excel VBA determine amount of characters
Replies
2
Views
347
Keebellah
Keebellah

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

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