Excel question

[Darkness]

Is the following possible in Excel and if so, how?

Assume cell E5 contains the text:

Piratecat

Let's say I want Excel to count the number of 'a's in the text in the cell (or several cells, e.g., E5:E12 or whatever, though that's secondary) and tell me this number (i.e., 2 in this case).

Bonus points if you can tell me how the function(s) in question (if there is one) is called in the German version of Excel. (I can figure it out even if I know only the English name, though. )

 

[Andre]

Is the following possible in Excel and if so, how?

Assume cell E5 contains the text:

Piratecat

Let's say I want Excel to count the number of 'a's in the text in the cell (or several cells, e.g., E5:E12 or whatever, though that's secondary) and tell me this number (i.e., 2 in this case).

Bonus points if you can tell me how the function(s) in question (if there is one) is called in the German version of Excel. (I can figure it out even if I know only the English name, though. )

Edited for clarity.

You can do both in Excel, but you'll need to use a number of functions. Specifically, FIND, COUNT, LOWER, and CONCATENATE.

FIND will show the position of a specified letter in a cell, reading from left to right. So looking for "a" in Piratecat will display "4" (the 4th letter). You can specify a starting position for FIND, so if you start at the 5th letter, the result will be "8" (the next instance of the letter "a"). If FIND reaches the end of the text without finding the letter specified, it will display the error "#VALUE!". Important: FIND is case-sensitive.

LOWER can be used to take the text in one cell and make it all lowercase. If "Piratecat" is in cell B1, and we put "=LOWER(B1)" in cell A1, A1 will display "piratecat".

COUNT will count the number of cells in a given range with a number. COUNT does not count errors, so it will return only the number of cells in which the FIND formula found a number.

CONCATENATE is a function to combine the text of several cells into one. FIND does not work across a range of cells, but you can concatenate all the cells into one, then use FIND on just that cell. CONCATENATE can combine up to 30 cells, though I believe you can't have more than 256 characters total in the cell with the combined text. (Alternately, you use separate formulas for each cell to be checked, then add the sums for each).

I've attached a spreadsheet showing both options, counting for just one cell, and counting for a group of cells. To change the starting position for FIND, I change the starting point to the previous result +1, e.g., if the previous result is "4", the next cell will start looking at the 5th letter. You'll see this in the spreadsheet.

Hope this helps!

 

[Darkness]

Yes, this certainly helps. I've used these functions before (some only rarely, but still) so I should be able to figure out how to make good use of this. Thank you very much!