Is there a way to paste a string value into a cells in a single column, a certain number of words after?

[MrJSP]

Sorry for the confusing title, perhaps an example better explains what I am trying to do.

If I have a list of cells that contain sentences such as :

A1 Mary Jane listening to music

A2 Peter Parker doing homework

A3 Eddie Brock eating pizza


and I would like to add a word after their names in this case perhaps "likes". Is there a way to command excel to paste the word "likes" after the first two given words in a cell?

so the completed version might look like:




A1 Mary Jane likes listening to music

A2 Peter Parker likes doing homework

A3 Eddie Brock likes eating pizza


What would be the function I would need to type to perform this on an entire column of data? Much appreciated

 

[jtakw]

Hi,

Use B1 formula copied down if you want the flexibility of inserting a user defined word (E2) at whatever number of words after (F2),
Or use C1 formula where the criterion is "hard-coded" in the formula:

Book1
	A	B	C	D	E	F
1	Mary Jane listening to music	Mary Jane likes listening to music	Mary Jane likes listening to music		Insert	after # of words
2	Peter Parker doing homework	Peter Parker likes doing homework	Peter Parker likes doing homework		likes	2
3	Eddie Brock eating pizza	Eddie Brock likes eating pizza	Eddie Brock likes eating pizza
Sheet297

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1," "," "&E$2&" ",F$2)
C1	=SUBSTITUTE(A1," "," likes ",2)

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-fo...ls-a-certain-of-words-after-initial-word.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html