Chat GPT; Excel; Extract First Letter of Second Name in a Cell

 Help me please with an Excel problem.  I have a two-column table with rows.   

• Column A is names (John Smith, Mary Jones, Tom Williams...)    
• Column B is emails (John@johnco, Mary@maryville, Tom@tomland, etc).    

I want to sort the rows by the LAST name of the people in Column A.    (Sorting by one letter is OK).   Now, one crude way is to make a Column C and manually put the first letter of the last name into it.   This would take about 20 minutes, at 10 rows per minute and 200 rows.  Can EXCEL identify the first letter of the second word (or, the same, the first letter of the last word) and place it alone in Column C?   What do you think?

ChatGPT

Yes, Excel can help you automate the process of extracting the first letter of the last name in Column A and placing it in Column C without manually typing out each letter. You can achieve this using a formula that extracts the first letter of the last word (assuming the last word in each cell of Column A is the last name). Here is how you can do it:

Formula Using RIGHT, LEFT, and FIND Functions:

If your names start in cell A1, you can use the following formula in cell C1 and drag it down to apply it to all rows:

excel

Copy code

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100)), 1)

This formula works by replacing each space " " with a large number of spaces (100 in this case), so the last word (last name) is pushed to the right-most part of the string. Then, it extracts the last 100 characters (which will now start with the last name due to the padding), trims these to remove the leading spaces, and finally takes the leftmost character (the first letter of the last name).

Sorting by the Extracted Letter:

After you have the first letter of the last name in Column C, you can sort your table by Column C. To do this:

Select your data range, including the headers.

Go to the Data tab on the Ribbon.

Click on "Sort." In the Sort dialog, choose to sort by Column C (or your corresponding label for this column), and select either Ascending or Descending order based on your preference.

This approach is much faster than manually typing the first letter of the last name for each row and can be completed in a matter of seconds, even with hundreds of rows.

##
To sort by 2 letters of last name (John Smith = SM)

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100)), 2)