Excel Tips - How many times does a letter appear in a cell ?

I want to be able to count the number of times a letter is used in a cell.
For example, if cell A2 contains the string "radar" I want to be able to
have excel return the number of times the letter "a" is used (2).

Hello,

I'm working with a fairly large spreadsheet involving many calculations and was wondering if someone could give me some tips on designing worksheets to minimimize calculation times.

I'm using Excel 2007. I believe that in previous versions of Excel, calculation times were slower if calculations used data on cells from different worksheets. I also think that calculation times were lengthened by using 'IF' formulae rather than 'OFFSET'.

Could someone tell me if the old rules for improving calculation speeds in previous versions of Excel still apply to Excel 2007?

Also, I'd like help with the following specific cases:
-I need to run calculations using two grids of data. Each grid contains approximately 40,000 rows and 2,000 columns of data. Would the calculations using data from both of these grids be faster if they were on the same worksheet than if they were on separate worksheets?

-In general, is it faster to have calculations split up over multiple worksheets than entirely on one worksheet?

If anyone has a link they can send me that addresses these general questions it would be much appreciated!

Thanks in advance!
Derek

I want to count the occurrence of certain letters in a range of cells. In my attachment I need the sum of how many times the letters "C,M,Y,K" occur in the range A2:D2.

I'm trying to find the correct formula which results in the total number of times a single letter occurs in a cell or range.
For example, I have the following 5 words, 1 word per cell

A1 EARMARK
A2 PERIMETER
A3 REGENERATION
A4 VAV
A5 WISP

If I want to know how many times the letter E occurs within range A1:A5, what is the formula to do that?

If I can't look at the whole range at once, how do I do it for a single cell?

I've tried using COUNTIF, FIND, MID, & SEARCH. None of them are giving me the right answer. I know there must be a combination that works. Any ideas?

I am looking to count how many times an "x" appears in a row up to 5 times. If it appears more than 5 times I want to stop counting that letter. What formula should I be using?

Hi,

I have just start using my client bank to export client records to excel when the client meets a certain search criteria, in this case an investment fund which we are recommend they disinvest from. Some clients hold the same funds in multiple portfolios and so appear several times or more on the exported excel sheet. Is there any way to get excel to recognise that there are repeats of certain clients and to change the spreadsheet automatically? Ideally, I would like each of the clients investments on the same row so I can use them all in a mail merge letter.

Any help/thoughts would be gratefully received!

Thanks,

Matt

Ok i was trying to think of a work around for a problem i have and this is what i came up with...but i dont know how to reference it in my formula....

For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.

=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")

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.
Code:

=SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)

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

Hi all,

Complete newbie here, have been reading various posts and trying to figure things out on my own but to no avail. Maybe someone can shed some light on my problem.

I'm using Excel 2004 (Mac) and want to create a VBA/macro that would change the background color of specific cells (say A4, A5, A6 and C8) based on the number OR letter in the cell. There is a drop-down from which the user must select either 0,1,2,3,4,5 or H for these cells. I want the color to change based on the user's selection as follows:

0 or 1 = Red (i.e. color value 3)
2 or 3 = Yellow (i.e. color value 6)
4 or 5 = Green (i.e. color value 4)
H = Lavender (i.e. color value 7)

I've found sample event code showing how to change the cell color based on a numerical value/range, as well as different sample code showing how to change the color based on the letter/text, but not *both in one*. (I've found out how to to insert the code via View Code when clicking the worksheet tab but am unfortunately not savvy enough to know how to set up the code correctly).

I can't imagine it would be too complex for someone with a decent level of VBA/macro knowledge. BTW, I would be using conditional formatting to do this except it only allows for 3 conditions/colors, whereas I need 4! (thanks MicroSoft...)

Any suggestions on how to do this would be very much appreciated!

Hello,

I've got Excel Beta 2007. My current problem has me unable to type in more than one letter/number in any cell. Also, the Excel options won't display when clicked.

Any advice? I have a feeling this has come up before but I'm unable to find it through searching.

Thanks.

Todd