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

Free Microsoft Excel 2013 Quick Reference

Formula to count occurrences of specific character in text string

Hi all!

Its there a way to count accuracies of a character in a string using "in cell" formula?

I want to avoid using VBA (it is so requested) and/or "Data"->"Text to Columns" option (I don’t know how to automate it for new entries without VBA, and don’t really have enough space).

=IF(NOT(ISERROR(FIND(" ";M2;1)));IF(NOT(ISERROR(FIND(" ";M2; FIND(" ";M2;FIND(" ";M2;1)+1))));"…";1);0)
This isn't an option do to limited nesting, and length limit. There should be up to 50 occurrences per entry.

Fare as I can tell there is no way to do this, but I'd like check first.

Thanks!


Post your answer or comment

comments powered by Disqus
I would like to find the position of the last occurrence of a character in a string. For example, I have a string with the following: "c:wwgpeToolbidsTest File.GP$". I would like to find out the position of the last '' in the string because I want to pull the filename, 'Test File.GP$' into a variable. I would like to avoid writing code to do this. Are there some Excel functions I can use for this?

Hi All,

I am after the formula to count the occurrence of, for instance the word 'the' in a sentence/paragraph that is contained in Cell A1. Cell B1 should return the quantity of times the word 'the' has been found in Cell A1.
Thanks in Advance,

Cheers,
Mark

Ladies and Gents, I am hoping you can help me here. I am trying to set up a sheet which will count the number of days worked in a given month less holidays. To do that I used Networkdays and that has worked fine. My problem is trying to count the number of specific days (like Mondays) in a given month but also wanted to make an allowance for any days like bank holidays which may appear in my list of Holidays. I am currently using =INT((($B$2-$A$2)-MOD(1-$A$2,7))/7) as the formula to count number of Mondays, Tuesdays etc but this wont account for the Holidays. Am I asking a bit much here?

Thanks in advance.

Trevor

Is there a way to count the occurences of multiple characters in a cell?
For example, the cell might contain: smith,john(1234), smith,mary(1234),
doe,john(2345), etc etc.

I would like to be able to count how many times (1234) appears in the cell.
I tried using LEN, but that only counts one character.

Mary-Lou

Hello,

Posted a problem last night regarding formula to count MIN of a column. Problem was solved. This problem is in the same vain as my last thread "Formula to count MIN with condition". In the attached worksheet samples, Col B has competition dates, Col C is time/distance data, Col C (filled down) is the formula that recogises if the time/distance is a PB. Once a season there is an event "REGION" where I want the formula to ignore the data in Col C adjacent to "REGION". The date of REGION changes every year and could be anywhere in Col B. The first attachment is what the sheet currently looks like and the second is what I want it to record, specifically D11. Even though D10 is a better distance, it would be ignored as it is REGION.

Thanks

RunHard

Hi all,

I'm looking for a formula to copy parts of an address in cells and paste in specific columns.

I attach a copy of a before and after (how i'd like it to look) sheet 1 - sheet 2.

Hope someone clever out there can help me with this.

Many Thanks

Marc

Hiv everyone

I would like to know if it is possible using a formula to evaluate the following.

I have a number of records that have in one cell a string of the form 1,2,3 etc (up to 10) representing conditions that have been met. There are a number of options available for producing reports on all of the other attributes in a record but now we wish to add this element as well.

The reports I can produce using VBA and the INSTR function, however on the summary page where, a total of all records matching available criteria is shown, depending upon drop down items being selected, I need to add this element to the equation as well. The existing conditions are counted using SUMPRODUCT and a combination of IF statements and work fine.

As there is an option to create a report for both AND and OR, e.g. if condition 1 and 2 and 3 apply include in the report, or if condition 1 or 2 or 3 apply include in the report

The conditions are selected using a check box and a drop down list to select the AND/OR condition.

I have been trying various combinations of database functions, countif, find and cannot get them to evaluate. I suspect that it may be beyond my reach to use a formula and I will need to use VBA with a Worksheet_Change macro to achieve what I want, or alternatively redesign the layout to store the conditions differently, however if anyone has any ideas for a formula I would much appreciate hearing from you.

A sample worksheet illustrates how the data is currently shown. The string in the record column is created using VBA and then inserted into the actual record.

Thanks for any ideas you may have

Regards

Jeff

Hi,

Can anyone help with worksheet function for finding position of numeric
character in a string.

I am trying to extract data into the form 12a345 from data that may be like
any below:

12a345
12a345c
12a345_c
12ab345
12ab345c
12ab345_c

Extracting the first bit is easy with LEFT() but to extract the 345 I need
to find it's position in the string then I can use MID().

--
Regards,

Paul.

Hi,

Can anyone help with worksheet function for finding position of numeric
character in a string.

I am trying to extract data into the form 12a345 from data that may be like
any below:

12a345
12a345c
12a345_c
12ab345
12ab345c
12ab345_c

Extracting the first bit is easy with LEFT() but to extract the 345 I need
to find it's position in the string then I can use MID().

--
Regards,

Paul.

I’m trying to count the occurrences of the character “@” in a range of cells.

I’ve found a couple formulas that work but they don’t update when a “@” is entered into a cell where it did not previously exist prior to entering the formula.

Is there any way to make a formula to count the number of times a specific word is found in a range of cells? EX: =count "agree"(C2-C10)" and this would return a number value for the number of cells with "agree" in cells C2-C10.

I am stumped as to how I can sum the number of times a specific date appears within a span of Start and End Dates.

In Column E (E3:E400) I have a list of Start Dates
In Column F (F3:F400) I have a list of End Dates

In one Row (R3:BZ3) I have a list of specific dates (i.e. 08/31/08, 09/03/08)

I have attempted to create a formula in the following Row (R4:BZ4) that counts the number of times the date in the row above (R3:BZ3) occurs within the span of Start & End Dates in Columns E & F.

I have attempted several iterations of the SUMPRODUCT and COUNTIF solutions that are posted on this board, but I have yet to find a situation such as mine.

Any assistance would be greatly appreciated.

Jim

Hi Gang,

I looked for quite some time trying to figure this out on my own but
no luck. I created an inventory spreadsheet that takes a download from
Oracle11i of all transactions posted within a 24-hour period. The
downloaded information is on one worksheet and a second worksheet in
the same file uses formulas to count various types of transactions and
calculate cost from the download. In the download there are various
columns containing quantity transacted, from and to departments,
transaction types and a reference section for users to input comments
when they transact. Usually the file has about 3000 rows. All of these
files have a date time stamp of when the transactions were processed.
Example: "6/18/2007 23:50". I believe the default format is "special"
for the date but I use a macro to change the format from military time
to regular time. (The macro also cleans up the initial download, which
has a lot of duplicate information).

Problem:
Where I work there are two shifts, one starts at 5:45 am to 5:45 pm
(days) and the other is from 5:45pm to 5:45am (nights). What I don't
know how to do is count the number of transactions that occurred
within the shift time frames. I want to avoid using a pivot table if
possible to keep down file size. Is there a formula instead that will
count the specific number of transactions that occurred within my
given time frames? I tried using a count if formula, but noticed if
the data is a "custom" or "date" format the formula will not work.
Also if possible I want to avoid creating another column to compute
the formula with. I guess I'm just looking for a formula to count the
time stamps within the criteria using the original column.

Objective:
To count how many transactions were posted on day and night shifts.

Sorry for the long post, just wanted to give as much detail as
possible.

Deepest thanks all!

Sam

Is there any formula to count the number of cells (and total the numerical
content of those cells) with a specific color pattern?

I'm using an array formula to count occurrences of certain text values in a
column. The column contains various 2-letter codes, and I want to count the
number of occurrences of codes starting with "L". I've tried using a wild
card character in the formula ("L?"), but it doesn't work. THis approach
works fine for regular formulas, but I think there's something to do with
Array formulas that prevents it from counting what I want.

Any suggestions out there?
--
PJB

What is the easiest way to count the number of specific charecters in a cell.

e.g = Cell A1 = Dog Cat Animal
number of As = 2
number of Spaces =2
number of Ds = 1

etc.

any help will be much appreciated.
Thanks,
Rennie

Can anyone please help me with a formula to count the number of times the letter "x" appears in a column.
A single "x" will be entered into a single cell

I'm using an array formula to count occurrences of certain text values in a
column. The column contains various 2-letter codes, and I want to count the
number of occurrences of codes starting with "L". I've tried using a wild
card character in the formula ("L?"), but it doesn't work. THis approach
works fine for regular formulas, but I think there's something to do with
Array formulas that prevents it from counting what I want.

Any suggestions out there?
--
PJB

Hi,

I have a range of cells in one sheet containing data as follows:

ABC
DEF_ABC
ABC_XYZ
.
.
and so on
In another sheet, I need to write one formula to count the number of times ABC occurs, another for the number of times DEF_ABC occurs and yet another for the number of times ABC_XYZ occurs. Note that DEF_ABC and ABX_XYZ need to considered distinct from ABC (i.e. not included in the count for ABC). This is only a sample list and the actual number of such strings I need to search for is much more. Is it possible to write a formula that satisfies these conditions?

Many thanks in advance!

Srikanth

I want to count the number of commas in a cell, if any.


	VB:
	
instr("A,B,C,D",",") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
tells me the location of the first comma (2), but I want the total number of commas to be returned (7). I've been using a laborious character-by-character check from 1 to len(string) and incrementing the variable CommaCount every time it finds a comma, but I'm pretty sure there's an easier way.

I need a formula to count the number of times the same thing occurs in a particular column. i.e. tree 1, tree 2, tree 3. I just tried the countif function and it is returning "0". Not sure if that's because the data I am looking for is part number/part text. Does this make a difference? There are also spaces between the number and the text. Also, the range I am asking excel to look in is based on a concatenate formula.

I need a formula to find the position of the SECOND occurrence of a character
in a string. I can find the first occurrence using the FIND function no
problem but I have no idea how to find the position of the second occurrence
formulaically. Is there a formula to do that without running VBA?

Hi,

In sheet "Dashboard" D5:L8 I want to count number of grades students get in each class group which is in column C. Class group will not remain constant and will change based on the data validation filter in C2. (I have kept Maths and English as an example)

Grades and Class group details from where results have to be calculated are in Sheet "Results"
Indirect reference ranges are in sheet "Indirect"

I tried using indirect countif formula but getting #Ref.

For example: Teaching group 11y/En1 has 17 students getting grade C (C3, C6 & C9)

Note: Grade A*= A*3, A*6 & A*9
Grade A = A3, A6 & A9, similar for other grades.

Could someone please let me know the formula that I can use to get the results.

Cheers,
Raj

hi
new to the forum but i have read through a lot of posts and can find one relating to my query

i have a spreadsheet with 5 pages (5 different products) to it that tracks the orders from customers and the savings made on each of these
i have the postcodes of the customers and i would like a formula to add the savings made in geographical regions i.e. south west, midlands, london etc
now ive used formulas that i got of here to be able to count the frequency of the postcodes
which is useful but i really need a formula that will take all the customers with london postcodes and add all the savings (across the 5 products) made in that region to give one total figure each month
output of the formula can be just a cell with figure then put into graph for visual presentation
i attach a copy of my sheet with some basic made up data in it
and the formula used to count frequency of "ab** ***" postcode
can anyone help please


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