0

on the below Image i have row data contains Grandfather's Name(Col G:G) ,Names.(Col F:F) but between every name on the cell it contains letter "."

so as shown on the Image the name is ("Mahmoud.Mostafa.Yousef.Radwan") watch mean the first name is (Mahmoud) ,2nd name is (Mostafa) ,3rd name Grandfather's Name is(Yousef)..

i did it already & used function to figure out the grandfather's name as shown in the image the function is :

=LEFT(RIGHT(RIGHT(F2,LEN(F2)-FIND(".",F2,1)),LEN(RIGHT(F2,LEN(F2)-FIND(".",F2,1)))-FIND(".",RIGHT(F2,LEN(F2)-FIND(".",F2,1)))),LEN(RIGHT(RIGHT(F2,LEN(F2)-FIND(".",F2,1)),LEN(RIGHT(F2,LEN(F2)-FIND(".",F2,1)))-FIND(".",RIGHT(F2,LEN(F2)-FIND(".",F2,1)))))-FIND(".",RIGHT(RIGHT(F2,LEN(F2)-FIND(".",F2,1)),LEN(RIGHT(F2,LEN(F2)-FIND(".",F2,1)))-FIND(".",RIGHT(F2,LEN(F2)-FIND(".",F2,1))))))

but as what you see.. ,the function depends on excluding "." many times as (=Find) function Determined the first letter i search for in cell.. so i have to use this till excluding all (Points ".") till i reach Grandfather's name..

if i use this heavy function on sheet contains Thousands of names ,sheet gonna be lagging on me & i need to Minimize this..

The Question is..

i want to find a function that can find a duplicate letter on the same cell & allowing me to start from whatever i need from those duplicate letters, to start specifically from that (Point ".") before Grandfather's Name 3rd letter,how can i do that?

Row Data

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Khaled Sayed
  • 70
  • 6
  • it's same problem if i use mid function like : =MID(F2,FIND(".",F2)+1,LEN(F2)-FIND(".",F2)) ,the result gonna be (Mostafa.Yousef.Radwan) – Khaled Sayed Jul 15 '20 at 18:01
  • so i have to complex it again by making it : =MID(MID(MID(F2,FIND(".",F2)+1,LEN(F2)-FIND(".",F2)),FIND(".",MID(F2,FIND(".",F2)+1,LEN(F2)-FIND(".",F2)))+1,LEN(MID(F2,FIND(".",F2)+1,LEN(F2)-FIND(".",F2)))-FIND(".",MID(F2,FIND(".",F2)+1,LEN(F2)-FIND(".",F2)))),1,FIND(".",MID(MID(F2,FIND(".",F2)+1,LEN(F2)-FIND(".",F2)),FIND(".",MID(F2,FIND(".",F2)+1,LEN(F2)-FIND(".",F2)))+1,LEN(MID(F2,FIND(".",F2)+1,LEN(F2)-FIND(".",F2)))-FIND(".",MID(F2,FIND(".",F2)+1,LEN(F2)-FIND(".",F2)))))-1) to get the result (Yousef) Grandfather's Name.. – Khaled Sayed Jul 15 '20 at 18:08
  • 1
    Did you try the linked dup: `=FILTERXML(""&SUBSTITUTE(F2,".","")&"","//s[position()=3]")`? – Scott Craner Jul 15 '20 at 18:12
  • i cannot use this ,it returns (#Value) when i drag it.. Note: i wasn't know this function before ,appreciate your effort – Khaled Sayed Jul 16 '20 at 03:41
  • okay it works will the problem was on the sheet. many thanks. – Khaled Sayed Jul 16 '20 at 03:45

1 Answers1

1

Instead of finding duplicates, your intension to find Grand FatherName(Word after 3rd '.') can use the data split options available under Data tab, here is the msdn link

In order to achieve with function commands then try below, which is still long but shorterthan the one you are using, =REPLACE(F2,1,SEARCH(".",F2,SEARCH(".",F2)+1),"").

Prasanna Grandhi
  • 26
  • 4
  • Hi there. Did you know you can make hypertext links in your comments look much prettier with this syntax: _`[link](http://example.com)`_? The different markdown you can use can be found in the _`help`_ link at the bottom and to the right of the comment box. Excellent first answer, by the way :) – deduper Jul 15 '20 at 19:22
  • Yeah thanks for the comment(first one). Updated. – Prasanna Grandhi Jul 15 '20 at 19:59
  • i can't use this one because result gonna be "Yousef.Radwan" & then when i use (left) function i have to use your function 2 times again just to find number of char's for name "Yousef".. thanks for your support.. – Khaled Sayed Jul 16 '20 at 02:33