Excel: Extracting nth word from cells


Hi, I am trying to extract the nth word (last name) from a cell following a unique combination of characters (ID #-). I do not want to use VBA, only functions. I need to extract the last name out of each cell which follows 'ID #-', which is not the first name, but the second name after the 'ID #-'. I notice there is a space before and after each last name, not sure if that information helps. Can anyone help? Let me know if you have any questions about it. Example:

PPD OF MASS H09 IOP H88 03/31/15 ID #-JOE SMITH TRACE# 46848 ACH SMITH
PPD OF MASS H10048 IOP H8778 03/31/15 ID #TRACY JOHNSON TRACE# 987 ACH JOHNSON
PPD OF MASS H09 IOP H88 03/31/15 ID #-MARY ADAMS TRACE# 46848 ACH ADAMS


This question generated 12 answers. To proceed to the answers, click here.

This thread is current as of May 06, 2015.


For more resources for Microsoft Excel