Is there any Formula which can count a particular alphabet from a string??

[rajranja]

Hi All,


Today only I have joined this group and on very first day I have a problem.


Can anyone help me in finding a way to count a particular alphabet into a string?


Example : if cell A1 = "Adsdfdfsfssfwaaasegrggrejgeigjklsfsgjksjgsgsafafsagerogbmdlb” and is there any function which can tell me how many time alphabet “A” is used into cell A1??


Waiting for your reply.


Regards,

rajranja

 

[xld]

If it is just capital A, then


LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))


If capital and lower-case A, then


=(LEN(A1)-LEN(SUBSTITUTE(A1,"A","")))+(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))

 

[xld]

This also works for the second case


=(LEN(A1)*2-SUM(LEN(SUBSTITUTE(A1,{"A","a"},""))))

 

[Luke M]

Another alternative formula, if you don't want case-sensitive:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"A",""))

 

[fred]

xld,


I understand len(Substitute(A1,{"A","a"},"")) would product 59. However, I don't understand how 113 was derived from adding the SUM(xxx) part. Could you please kindly shed some lights on this? Thank you very much.

 

[xld]

fred,


LEN(SUBSTITUTE(A1,{"A","a"},"") doesn't produce 113 per se, it produces an array of two values. It does two substitutions, one for A and one for a. The first returns 59 characters, the second 54, giving a total of 113. If you notice, I do a LEN(A1)*2 because I am looking for two letters, so it takes that 113 away from 60 times 2, giving a final result of 7.


If you select the LEN(SUBSTITUTE(A1,{"A","a"},""))part of the formula in the formula bar, and then hit F9, you will see it shows that part evaluated as {59,54}, showing what is going on.

 

[rajranja]

Thanks guys.. i found this answer 2-3 hrs after i posted this question..but i have some more questions..


if i have few Paths this below..

1.C:DocumentsrajivHome LoanNew FolderNew Folderraj.xls

2.C:Documents and SettingsrajivMy DocumentsHomeNew FolderNew Folderraj_2010.xls

3.C:Documents and SettingsHome LoanNew FolderNew Folderrajiv-ranjan.xls


and i only need to extract the File name (raj.xls,raj_2010.xls...) where Len(file name) is not fixed.


using your fomula i can count the number of "" into this string but how to get the position of the last ""?? if we get the position of last then it would be easy to get the exact file name.


i can do this via VBA codes but i want it with excel formula's ONLY?


Can any budy suggest of this??


I have started loving this Site Thanks Chandoo.

 

[Hui]

Rajranja

Use this as an Array formula


=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=$B$1)*ROW(INDIRECT("A1:A"&LEN(A1)))))

Enter with Ctrl Shift Enter


Assumes you have your data in A1:A3

and in B1

 

[oldchippy]

Hi,


Here's a slightly shorter formula and without the use of column B


=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT(" ",100)),99))

 

[rajranja]

oldchippy <> Hey thanx ,its working Thanks a ton for this help.


Hui<> Hey Thanx for this.. its bit confusing but it also worked


Thanks Guys..