Extracting nth word from cells

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
749
Office Version
  1. 365
Platform
  1. Windows
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>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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))
 
Upvote 0
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...
 
Upvote 0
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))
 
Upvote 0
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"?
 
Upvote 0
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!!!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top