add text before and after first occurrence string

[Okoth]

I want to find a string in one cell and add text before and after the first occurrence of that string. An example is easier to explain. I want to add <strong> before time and </strong> after time. What is important is that it only happens with the first occurrence of the word time. A1 is the original text and B1 is the result of the formula.

	A​	B​
1​	This is the first time the string occurs and this is the second time it shows up.	This is the first<strong>time</strong> the string occurs and this is the second time it shows up.

To get <strong> before the first "time" wasn't hard. I managed with:

=MID(A1;1;FIND("time";A1)-1)&"<strong>"&MID(A1;FIND("time";A1);9999)

but how do I get </strong> after the first "time" in A1? I want to do this with a formula not VBA.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this

21 12 12.xlsm
	A	B
1	This is the first time the string occurs and this is the second time it shows up.	This is the first <strong>time</strong> the string occurs and this is the second time it shows up.
Sheet2 (2)

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(A1,"time","<strong>time</strong>",1)

 

[AlanY]

try

=SUBSTITUTE(A1;"time";"<strong>"&"time"&"<strong>";1)

 

[Okoth]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks, I will. I haven't been here for a long time. The site has changed for the better.

Thank you both for the answer.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.