Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Count occurrence of character within a cell Results

I would like to count the number of a particular character within a cell. In
the cell are store numbers/names separated by the | character. The store
number/name represents a stop that the truck made. There is always one more
| character than stops. An example is: PER | R7 | PER is one stop at R7.
Another example is: BAK | 80 | 42 | BAK is 2 stops- one at 80 and one at 42.
Yes there is a space before and after the | character and PER or BAK is where
the truck started and ended. Thank you for any help.

Hi,

I'm trying to count the number of occurnaces of a character within a cell to use as criteria within an If statement in a Macro.... For example

Hi/This is/ Randy
Hi/this is Randy

I'd like to count the number of "/" within the cell 2 for the first, 1 for the second.

I have a range, 11 wide by 47 tall. For discussion, we'll say it is 10 by 50.
I have strings of text within these cells, which may or may not include a
small string of text in the middle of the strings, such as "(LA)" within the
string "CA 101 - Introduction to Computers (LA) (CA 99, FND 101)" .
Each cell will be different, but many will have "(LA)" or other 4 or 5
character strings within the larger strings, like the one above.
I need a formula (hopefully in one cell) to count the number of times or
cells such a string "(LA)" appears within the larger strings in the cells in
this range.

Thanks in advance for giving your time to this dilemma.

I have a range, 11 wide by 47 tall. For discussion, we'll say it is 10 by 50.
I have strings of text within these cells, which may or may not include a
small string of text in the middle of the strings, such as "(LA)" within the
string "CA 101 - Introduction to Computers (LA) (CA 99, FND 101)" .
Each cell will be different, but many will have "(LA)" or other 4 or 5
character strings within the larger strings, like the one above.
I need a formula (hopefully in one cell) to count the number of times or
cells such a string "(LA)" appears within the larger strings in the cells in
this range.

Thanks in advance for giving your time to this dilemma.

Hi,

I was reading this post on how to count the number of occurrences of a word in a range of cells and came across an issue. Here's the formula that was recommended, which works well under certain circumstances.
If I want to search for a single word "a" in a cell with the following content, using a version of this formula
(I've attached an example sheet), it returns a value of 5: "This text contains no single character but it does have the
letter I'm searching for."

The answer I'm looking for is 0, but as the letter "a" appears 5 times it counts them all. Is there any way to specify it took look for the exact contents of the cell?

Cheers,

3/5

I know i saw something here a while ago but have looked to no avail, so if I'm repeating an already stated question, I apologize.

=MID('Tracking Links'!C17,FIND(".",'Tracking Links'!C17)+1,FIND("m",'Tracking Links'!C17)-(FIND(".",'Tracking Links'!C17)))

This works, but it will not take into account .org, .uk, etc domain variations as the find is based on the m in .com. Since domains could also contain an "m" as well, have been trying to write a formula to identify the 3rd occurrence of "/" within a cell to count characters and return all text between, but haven't been able to take into account mobile web addresses either as in that case I would want to look up if "www" is present else return text after "//" in http://

Would this require and iserror formula? sorry, trying to teach myself and could use a pointer

Thanks!

Hi Guys,

Really struggling with a query in excel.

I am trying to use SUMIF to count the occurrences of a 3 character alphanumeric entry of text in a series of cells.

I have a simple version which works however it counts the cells which have that occurrence in not the amount of times that string occurs.

I know it would be better to rewrite the spreadsheet but that is not possible due to certain limitations.

In this example the text in the field is

"UH X4 - 314657 R01 22/02/09 - 314972 R09 03/05/09 - 315627 R01 09/06/09 - 315932 R09 25/06/09"

This is telling me

UH = Unacceptable history
X4 = 4 Jobs have been done to this job
xxxxxx = the job number
XYY = The person working on it
Date, the date the job was completed

I have about 30 other rows similar to this within the column and I need to count all the times R01 and R02 occur in the whole column, not the ammount of cells that occurrence is in.

This is what I am using to cound that column.

=SUM(COUNTIF($N$6:$N$1000,"*R01*"))

This calculates how many Cells R01 appears in however as you can see above R01 appears twice, I need it to count that as 2 not 1.

Also if there is some way I can extract the dates from this field and calculate a mean average between the dates and flag up dates that are 3 weeks apart or less that would be great.

Any help would be appreciated, I'm sorry for rambling on but I'm really stuck on this one.

Thanks
Ross

hi

I've been writing a program to calculate the percentage frequency of letter occurrence within a string ; it's meant to be used as a tool for breaking simple ciphers. I keep getting a 'type mismatch' error - can anyone tell me why?

Sub freqan()

'work out the length of the cipher and store as variable "lenstr"
Dim lenstr As Integer
lenstr = Len(Cells(1, 2))
'make a (26,3) array
Dim myarray(26, 3)

'Put the letters a-z in the first column

myarray(1, 1) = "A"
myarray(2, 1) = "B"
myarray(3, 1) = "C"
myarray(4, 1) = "D"
myarray(5, 1) = "E"
myarray(6, 1) = "F"
myarray(7, 1) = "G"
myarray(8, 1) = "H"
myarray(9, 1) = "I"
myarray(10, 1) = "J"
myarray(11, 1) = "K"
myarray(12, 1) = "L"
myarray(13, 1) = "M"
myarray(14, 1) = "N"
myarray(15, 1) = "O"
myarray(16, 1) = "P"
myarray(17, 1) = "Q"
myarray(18, 1) = "R"
myarray(19, 1) = "S"
myarray(20, 1) = "T"
myarray(21, 1) = "U"
myarray(22, 1) = "V"
myarray(23, 1) = "W"
myarray(24, 1) = "X"
myarray(25, 1) = "Y"
myarray(26, 1) = "Z"

'dim the second column to show the count of letters, by subtracting the length of the string after desired
'character has been removed from the length of the string beforehand
myarray(1, 2) = lenstr - Len(Replace(Cells(1, 2), "a" Or "A", ""))
myarray(2, 2) = lenstr - Len(Replace(Cells(1, 2), "b" Or "B", ""))
myarray(3, 2) = lenstr - Len(Replace(Cells(1, 2), "c" Or "C", ""))
myarray(4, 2) = lenstr - Len(Replace(Cells(1, 2), "d" Or "D", ""))
myarray(5, 2) = lenstr - Len(Replace(Cells(1, 2), "e" Or "E", ""))
myarray(6, 2) = lenstr - Len(Replace(Cells(1, 2), "f" Or "F", ""))
myarray(7, 2) = lenstr - Len(Replace(Cells(1, 2), "g" Or "G", ""))
myarray(8, 2) = lenstr - Len(Replace(Cells(1, 2), "h" Or "H", ""))
myarray(9, 2) = lenstr - Len(Replace(Cells(1, 2), "i" Or "I", ""))
myarray(10, 2) = lenstr - Len(Replace(Cells(1, 2), "j" Or "J", ""))
myarray(11, 2) = lenstr - Len(Replace(Cells(1, 2), "k" Or "K", ""))
myarray(12, 2) = lenstr - Len(Replace(Cells(1, 2), "l" Or "L", ""))
myarray(13, 2) = lenstr - Len(Replace(Cells(1, 2), "m" Or "M", ""))
myarray(14, 2) = lenstr - Len(Replace(Cells(1, 2), "n" Or "N", ""))
myarray(15, 2) = lenstr - Len(Replace(Cells(1, 2), "o" Or "O", ""))
myarray(16, 2) = lenstr - Len(Replace(Cells(1, 2), "p" Or "P", ""))
myarray(17, 2) = lenstr - Len(Replace(Cells(1, 2), "q" Or "Q", ""))
myarray(18, 2) = lenstr - Len(Replace(Cells(1, 2), "r" Or "R", ""))
myarray(19, 2) = lenstr - Len(Replace(Cells(1, 2), "S" Or "S", ""))
myarray(20, 2) = lenstr - Len(Replace(Cells(1, 2), "t" Or "T", ""))
myarray(21, 2) = lenstr - Len(Replace(Cells(1, 2), "u" Or "U", ""))
myarray(22, 2) = lenstr - Len(Replace(Cells(1, 2), "v" Or "V", ""))
myarray(23, 2) = lenstr - Len(Replace(Cells(1, 2), "w" Or "W", ""))
myarray(24, 2) = lenstr - Len(Replace(Cells(1, 2), "x" Or "X", ""))
myarray(25, 2) = lenstr - Len(Replace(Cells(1, 2), "y" Or "Y", ""))
myarray(26, 2) = lenstr - Len(Replace(Cells(1, 2), "z" Or "Z", ""))

'create a third column to show percentages

myarray(1, 3) = (myarray(1, 2) / lenstr) * 100
myarray(2, 3) = (myarray(2, 2) / lenstr) * 100
myarray(3, 3) = (myarray(3, 2) / lenstr) * 100
myarray(4, 3) = (myarray(4, 2) / lenstr) * 100
myarray(5, 3) = (myarray(5, 2) / lenstr) * 100
myarray(6, 3) = (myarray(6, 2) / lenstr) * 100
myarray(7, 3) = (myarray(7, 2) / lenstr) * 100
myarray(8, 3) = (myarray(8, 2) / lenstr) * 100
myarray(9, 3) = (myarray(9, 2) / lenstr) * 100
myarray(10, 3) = (myarray(10, 2) / lenstr) * 100
myarray(11, 3) = (myarray(11, 2) / lenstr) * 100
myarray(12, 3) = (myarray(12, 2) / lenstr) * 100
myarray(13, 3) = (myarray(13, 2) / lenstr) * 100
myarray(14, 3) = (myarray(14, 2) / lenstr) * 100
myarray(15, 3) = (myarray(15, 2) / lenstr) * 100
myarray(16, 3) = (myarray(16, 2) / lenstr) * 100
myarray(17, 3) = (myarray(17, 2) / lenstr) * 100
myarray(18, 3) = (myarray(18, 2) / lenstr) * 100
myarray(19, 3) = (myarray(19, 2) / lenstr) * 100
myarray(20, 3) = (myarray(20, 2) / lenstr) * 100
myarray(21, 3) = (myarray(21, 2) / lenstr) * 100
myarray(22, 3) = (myarray(22, 2) / lenstr) * 100
myarray(23, 3) = (myarray(23, 2) / lenstr) * 100
myarray(24, 3) = (myarray(24, 2) / lenstr) * 100
myarray(25, 3) = (myarray(25, 2) / lenstr) * 100
myarray(26, 3) = (myarray(26, 2) / lenstr) * 100


'paste array to worksheet

[C3:E28] = myarray

End Sub
Thanks,
hazzacanary


No luck finding an answer? You could always try Google.