| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count the match letter within a string?
Does anyone have any suggestions on how to count the match letter within
string? such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which is 5. Does anyone have any suggestions? Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the match letter within a string?
With
A1: RRRR123RR This formula returns the count of R's in A1 B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"R","")) In this instance, B1 returns 6 Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Eric" wrote: Does anyone have any suggestions on how to count the match letter within string? such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which is 5. Does anyone have any suggestions? Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the match letter within a string?
On Tue, 21 Aug 2007 18:32:05 -0700, Eric
wrote: Does anyone have any suggestions on how to count the match letter within string? such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which is 5. Does anyone have any suggestions? Eric =LEN(A1)-LEN(SUBSTITUTE(A1,"R","")) You can use a cell reference containing your match_letter in place of the "R" in the above example. --ron |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the match letter within a string?
Try this:
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R","")) Note: SUBSTITUTE is case sensitive so the "R" needs to be in uppercase. =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R","")) = 5 =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"r","")) = 0 -- Biff Microsoft Excel MVP "Eric" wrote in message ... Does anyone have any suggestions on how to count the match letter within string? such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which is 5. Does anyone have any suggestions? Eric |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the match letter within a string?
Thank you very much for suggestions
Eric "Ron Coderre" wrote: With A1: RRRR123RR This formula returns the count of R's in A1 B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"R","")) In this instance, B1 returns 6 Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Eric" wrote: Does anyone have any suggestions on how to count the match letter within string? such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which is 5. Does anyone have any suggestions? Eric |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the match letter within a string?
I'm glad you could work with that......thanks for the feedback.
*********** Regards, Ron XL2003, WinXP "Eric" wrote: Thank you very much for suggestions Eric "Ron Coderre" wrote: With A1: RRRR123RR This formula returns the count of R's in A1 B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"R","")) In this instance, B1 returns 6 Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Eric" wrote: Does anyone have any suggestions on how to count the match letter within string? such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which is 5. Does anyone have any suggestions? Eric |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the match letter within a string?
To solve for its being case sensitive, use UPPER at both
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("r"),"")) "T. Valko" wrote: Try this: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R","")) Note: SUBSTITUTE is case sensitive so the "R" needs to be in uppercase. =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R","")) = 5 =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"r","")) = 0 -- Biff Microsoft Excel MVP "Eric" wrote in message ... Does anyone have any suggestions on how to count the match letter within string? such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which is 5. Does anyone have any suggestions? Eric |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count the match letter within a string?
You could do that but if you know the letter needs to be in uppercase why
use UPPER("r") when you can just use "R"? To make it foolproof it would be better to use a cell to hold the letter then: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(A2),"")) Where A2 = R or r -- Biff Microsoft Excel MVP "Tevuna" wrote in message ... To solve for its being case sensitive, use UPPER at both =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("r"),"")) "T. Valko" wrote: Try this: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R","")) Note: SUBSTITUTE is case sensitive so the "R" needs to be in uppercase. =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R","")) = 5 =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"r","")) = 0 -- Biff Microsoft Excel MVP "Eric" wrote in message ... Does anyone have any suggestions on how to count the match letter within string? such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which is 5. Does anyone have any suggestions? Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| letter number count | Excel Worksheet Functions | |||
| I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW | Excel Worksheet Functions | |||
| How can I count the number of times a letter repeats in a string? | Excel Worksheet Functions | |||
| count a number range and a letter in a cell | Excel Worksheet Functions | |||
| count cells in a coloumn with letter x in it | Excel Worksheet Functions | |||
Linear Mode
