Extracting nth word from cells

[CyrusTheVirus]

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

<tbody>
</tbody>

 

[Mike LH]

Hi,

The formula works by inserting spaces between the words and the 255 is arbitrary. The only rule for that number is that it must be large enough to get us to the start of the word we want to extract; or at least the space in front of it, and go past the last letter of the word we want. So if we want to extract the third word from this string

A Very happy birthday

We need 6 to get us to the space before our word and a further 5 for the letters in the word happy so 11 would do. In reality of course we don't know how many we need so 255 is a pretty safe bet.

 

[Mike LH]

Hi Mike, I had thought about that. I actually didn't post my full cell of data (it's really long), but I did notice that each last name is the 14th word in the 'actual' cell. I had thought that a 14th word extraction could do it, but had no idea what the actual formula would be. So, thanks!

Though, I did try changing 10 to 14 in my cell, it doesn't seem to work. Maybe i'll try figuring out what that all means in that formula you wrote.

Hi,

14 will extract the 14th word but IMO we got lucky in your first set of data in that the word you wanted in each was the 10th and the longer a string becomes the less chance there is of that being a safe assumption. I believe searching for a key in front of the word is the way to go.