• 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.

How to find position of repeated (duplicate) word or letter by FIND formula

S

Sandy_Patel

New Member
Hello

I want to find position of repeated (duplicate) word or letter from below sentence.

John is at home which is nearby

I want to find position number of repeated “o” from the above sentence, position of that is 13 but do not know, how to find it by formula of FIND or other way ?

Syntax of find formula is FIND(find_text, within_text, [start_num])

Could anyone explain me , what is the use of “start_num” in above syntax?


Thanks
 
Hui said:
=FIND("o",A1,FIND("o",A1)+1)
Click to expand...

Hi Hui
My question still unresolved to some extent. I want to learn how find out any position of repeated letter in sentence by formula. For example, I want to find 3rd, 4th or 5th repeated letter in sentence.


Assume I have below sentence which is slightly different from previous one.
John is at home which is on nearby.
Now I want to find out position of 3rd repeated letter as “o” which is 26.

I tried below formulas and get result as .

=FIND("o",A1,FIND("o",A1)+2) Result of this as 13
=FIND("o",A1,FIND("o",A1)+12) : Result of this as 26 which is right.


I have gone through link that you posted in earlier reply and got good idea of formula FIND. But it still does not give through idea to find any position of repeated letter in sentence.

I tried to understand syntax of FIND. It said meaning of Start_num syntax as below.
Start_num Optional. Specifies the character at which to start the search. The first character
in within_text is character number 1. If you omit start_num, it is assumed to be 1.

What does it mean by as “specifies the character at which to start the search” ? How to use this function(Start_num) in Find formula ?


Please tell me how to find any position of repeated letter in sentence by formula ?



Thanks
 
Hello Sandip,

One way is to use SUBSTITUTE

=FIND("^",SUBSTITUTE(A1,"o","^",3))

SUBSTITUTE has 4th argument [instance_num]. If we omit this field will replace ALL [old_text] mentioned on the formula, otherwise the mentioned instance number.

eg:

=SUBSTITUTE(A1,"o","^",3)

3rd instance of "o" will be changed to "^" entered in A1. If we change 3 to 2 will change 2nd instance of "o" etc..

then you can use this method inside FIND

=FIND("^",SUBSTITUTE(A1,"o","^",3))

Which means,

1. SUBSTITUTE 3rd "o" to "^" in A1
2. FIND substituted "^" position

You can use any character/word you wish to use rather than "^". Make sure those are not appear in A1.

Note: FIND & SUBSTITUTE are CASE sensitive.
 
Hui said:
Did you read the link i posted above ?
http://chandoo.org/wp/2012/05/17/formula-forensic-no-021/
Click to expand...

Hi Hui

I have opened this link http://chandoo.org/wp/2012/05/17/formula-forensic-no-021/ and tried to understand this formula =FIND(“\”,A1,FIND(“\”,A1,FIND(“\”,A1,FIND(“\”,A1)+1)+1)+1)
I have read all steps of explanation of this formula but still I am not able to understand how it works.
I also read information of Find and Substitute function on this link but failed to understand on my first sight. But having read reply of Haseeb, I understood using combination of Find and Substitute formula to find position of any repeated letter or text.


So I got solution to my question.


One question, how can I change my name on this forum ? I already emailed regarding this matter to hello@chandoo.org but have not got any reply. Please advise me if I have to send mail to different email.

Thanks a lot Hui

Enjoy weekend


Cheers
 
Hi Haseeb

This is excellent information given by you. I also read same sort of information on other website by google but you have explained systematically consequently very easy for me to grasp it.

Appreciate your support

Cheers[/quote]
 
You must log in or register to reply here.
Back
Top