Extract the last substring from a cell

by

This works, even when there are middle names:

=MID(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2))

If you want everything BUT the last name, check out this answer.

If there are trailing spaces in your names, then you may want to remove them by replacing all instances of A2 by TRIM(A2) in the above formula.

Note that it is only by pure chance that your first formula =RIGHT(A2,FIND(" ",A2,1)-1) kind of works for Alistair Stevens. This is because "Alistair" and " Stevens" happen to contain the same number of characters (if you count the leading space in " Stevens").

More Related Contents:

  1. Excel: Searching for multiple terms in a cell
  2. Calculation is wrong, Rounding wrong
  3. Sequence of numbers based on start and end value of 2 cells [closed]
  4. I have an Excel data sheet that I would like to extract data from into a new sheet
  5. Error in finding last used cell in Excel with VBA
  6. Getting unique values in Excel by using formulas only
  7. Vlookup using 2 columns to reference another
  8. filter out multiple criteria using excel vba
  9. Ignore Duplicates and Create New List of Unique Values in Excel
  10. Excel Formula for Inventory
  11. Two column lookup in table array using INDEX and MATCH
  12. Closing Excel Application using VBA
  13. Using UNIQUE with non-adjecent columns on different sheets
  14. Concatenate column headers if value in rows below is non-blank
  15. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  16. If two cells match, return value from third
  17. What does — do in Excel formulas?
  18. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  19. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  20. Using the value in a cell as a cell reference in a formula?
  21. Countif With Multiple OR Criteria
  22. Excel – extracting top 5 values
  23. Importing CSV with line breaks in Excel 2007
  24. Excel VBA to match and line up rows
  25. CountIf With Filtered Data
  26. How to count cells in a range with a value less than another cell in excel?
  27. Excel Date Conversion from yyyymmdd to mm/dd/yyyy
  28. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  29. Delete all data rows from an Excel table (apart from the first)
  30. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?

Leave a Comment