• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

rajranja

New Member
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
 
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","")))
 
Another alternative formula, if you don't want case-sensitive:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"A",""))
 
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.
 
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.
 
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.
 
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
 
Hi,


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


=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT(" ",100)),99))
 
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..
 
Back
Top