Ho do I extract just first name and count the most popular names?

In B2 Cell

=IFERROR(LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1),"")

Drag it down..

Don't know what you are trying to say with the below statement will be helpful if you tell/show the expected result

count the most popular name

Mate,
May I suggest you mark this thread as "Closed" (considering that your original request has been answered to your satisfaction). Ideally, a follow-up query should be raised via a new thread and provide a link to this thread.

Anyways, to find top 20, we can take a couple of approaches.

1. Edit: Create a non-duplicate list of first names. Else the same name would be repeated many-times over.
2. Evaluate frequencies as before and then sort the non-duplicate list in descending order of frequency. So highest frequencies would come to the top.
3. Use Rank formula to rank the frequencies of the non-duplicate list based on highest frequency = Rank 1, next highest Rank 2 and so on...; and then sort based on Rank (lowest on top).

Unfortunately, the data in your original sheet is very limited to showcase either of these approaches. However, if you have a larger dataset - you can try it yourself.

In C2 Cell

=IF(B2="","",VALUE(COUNTIF($B$2:$B$8,B2)&"."&ROWS($1:1)))

Drag it down..



In F2 Cell

=IFERROR(INDEX($B$2:$B$8,MATCH(LARGE($C$2:$C$8,ROWS($1:1)),$C$2:$C$8,0)),"")

Drag it down..