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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
Calculation With Numbers Separated By Hyphen
Replies
9
Views
309
honkin
H
S
  • Question Question
Excel Formula to build out Combinations/Permutations of a series of numbers
Replies
1
Views
197
Eric W
Eric W
Keebellah
  • Question Question
Excel VBA determine amount of characters
Replies
2
Views
310
Keebellah
Keebellah
T
  • Solved
How to return comma separated data (already set up) and return data into separately (matched) cells
Replies
3
Views
127
Fluff
Fluff
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
158
sijpie
sijpie

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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