How to find name from middle name?

[bhasoriya]

I have name list. like..


paragatulbhaishah

parthatulbhaishah

dhruvatulbhaishah

There is no space between name, middle name and surname.


now i want to search how much son of atulbhai? and names of them.


Please guide/help me.

 

[Faseeh]

Hi bhasoriya,


With your data in A1:A3 & "atulbhai" in B1, use following formulas:


For Count :

=SUM(IFERROR((SEARCH($B$1,$A$1:$A$3)>=1),0)*1)

For Names:


=IFERROR(INDEX($A$1:$A$3,IF(LARGE(IFERROR((SEARCH($B$1,$A$1:$A$3)>=1),0)*ROW($A$1:$A$3),ROW(A1))=0,NA(),LARGE(IFERROR((SEARCH($B$1,$A$1:$A$3)>=1),0)*ROW($A$1:$A$3),ROW(A1))),0),"")


...and drag down. For both press Ctrl+Shift+Enter


Regards,

Faseeh

 

[shrivallabha]

Hi bhasoriya,


If this list is in Column A and word "atulbhai" is in B1 then a formula like:

=COUNTIF(A1:A100,"*"&B1&"*")


will give count of rows where name atulbhai comes.


And to get the list where atulbhai comes repeat formula COUNTIF times down. This is ARRAY formula so needs to be CTRL + SHIFT + ENTERed:

=INDEX($A$1:$A$3,SMALL(IF(ISNUMBER(SEARCH(B1,$A$1:$A$3,1)),ROW($A$1:$A$3)),ROWS($A$1:$A1)))

 

[bhasoriya]

Hello Shrivallabha,


Thanks for response.


Countif formula works perfect, but there were require to explain more regarding first name.


Please check here uploaded link for more details.


Thanks all..

 

[bobhc]

Good day bhasoriya


Could you not use the Left,Mid and Right functions to split your names up?


http://chandoo.org/wp/2008/09/08/split-text-excel-functions/

 

[3G]

Faseeh's post is a perfect Formula Forensics entry!

 

[shrivallabha]

Hi Bhasoriya,


I can't see any upload link above.

 

[bhasoriya]

Sorry Shrivallabha,


Missed to paste here link,


Please find below download link

https://rapidshare.com/#!download|116p11|4208871131|First%20Middle%20Lastname.xlsx|12|0|0

 

[NARAYANK991]

Hi Bhasoriya ,


Check this file here :


http://speedy.sh/NJ4f3/First-Middle-Lastname.xlsx


Narayan

 

[bobhc]

Good day bhasoriya


You may find this upload of use, different ways to skin a cat


https://dl.dropbox.com/u/75495784/Copy%20of%20Split_Cells.xlsm

 

[bhasoriya]

Hello Narayank991,


Thanks a lot... it is perfect.


Thanks to bobhc also.

you gave me excellent collection of those formula variation.