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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]PPD OF MASS H09 IOP H88 03/31/15 ID #-JOE SMITH TRACE# 46848 ACH
[/TD]
[TD]SMITH
[/TD]
[/TR]
[TR]
[TD]PPD OF MASS H10048 IOP H8778 03/31/15 ID #TRACY JOHNSON TRACE# 987 ACH
[/TD]
[TD]JOHNSON
[/TD]
[/TR]
[TR]
[TD]PPD OF MASS H09 IOP H88 03/31/15 ID #-MARY ADAMS TRACE# 46848 ACH
[/TD]
[TD]ADAMS
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike LH]

Hi,

Assuming your first strin is in A2 then put this in a cell and drag down as required.


=TRIM(MID(SUBSTITUTE(" " & MID(A2,FIND("#",A2),LEN(A2))&REPT(" ",6)," ",REPT(" ",255)),2*255,255))

 

[Jonmo1]

Try this..

=TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND("TRACE#",A1)-1,LEN(A1),"")," ",REPT(" ",255)),255))


I found it easier to focus on the existence of "TRACE#" 'After' the last name...

 

[Rick Rothstein]

See my mini-blog article here for a generalized formula for finding the nth field in a delimited text string...

<!-- title / author block --> [h=3]Get Field from Delimited Text String[/h]

 

[Mike LH]

Hi,

Looking again at the samples you posted, in all cases the second name is the 10th word in the cell using Space as the word delimiter. If that's reliable for all data then you can use this which is simpler. Note that the 10 towards the end makes the formula pull out the 10th word.


=TRIM(MID(SUBSTITUTE(" " & A2&REPT(" ",6)," ",REPT(" ",255)),10*255,255))

 

[CyrusTheVirus]

Hi,

Assuming your first strin is in A2 then put this in a cell and drag down as required.


=TRIM(MID(SUBSTITUTE(" " & MID(A2,FIND("#",A2),LEN(A2))&REPT(" ",6)," ",REPT(" ",255)),2*255,255))

Mike, thank you! This works great!. I'm not sure what this all means yet. What does the last bit mean, the "2*255, 255"?

 

[CyrusTheVirus]

Try this..

=TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND("TRACE#",A1)-1,LEN(A1),"")," ",REPT(" ",255)),255))


I found it easier to focus on the existence of "TRACE#" 'After' the last name...

Jonmo1 Thanks! This work great too! You guys are too awesome! Never thought about using 'TRACE#'. I'll have to study this to understand it. Thanks again!!!

 

[CyrusTheVirus]

See my mini-blog article here for a generalized formula for finding the nth field in a delimited text string...

<!-- title / author block --> Get Field from Delimited Text String

Hi Rick, Thanks for letting me know! I'll be sure to check it out! I've been really confused on this problem, you all have been very helpful.

 

[Jonmo1]

Glad to help, thakns for the feedback.

 

[CyrusTheVirus]

Hi,

Looking again at the samples you posted, in all cases the second name is the 10th word in the cell using Space as the word delimiter. If that's reliable for all data then you can use this which is simpler. Note that the 10 towards the end makes the formula pull out the 10th word.


=TRIM(MID(SUBSTITUTE(" " & A2&REPT(" ",6)," ",REPT(" ",255)),10*255,255))

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.