Find and Replace

[Dottiehelp]

HELP!

I am trying to use find and replace but the text that i'm searching for appears three times in the cell. I only need to replace the first occurrence in the cell. Does anyone know if this can be done? Alternatively, if there is a way to do this, can the second and third occurrence be changed?

example:

Cheryl called Louie to advise she would be late for the meeting. Louie responded that he would meet Cheryl at her office. Cheryl confirmed.

I need to change the first 'Cheryl' to a job title and the second and third Cheryl to her initials (CL) so would read:

Manager of Aboriginal Affairs called Louie to advise she would be late for the meeting. Louie responded that he would meet CL at her office. CL confirmed.

Help would be appreciated. thanks!!!

[protonLeah]

=SUBSTITUTE(text,old_text,new_text,instance_num)

[Dottiehelp]

Hi ProtonLeah, thank you so much for your response, sorry, I guess I'm abit green, where am I supposed to put that? in the find box after I type Cheryl?

[protonLeah]

Sorry, that's a cell formula. It won't work for your case.

[Dottiehelp]

ok, thank you for responding.

[sktneer]

Assuming your text is in A1, then try this to see if it gives you the desired result.........

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Cheryl",UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)-1,1)),3),"Cheryl",UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)-1,1)),2),"Cheryl","Manager of Aboriginal Affairs")

[Dottiehelp]

oh my gosh, it worked!!!! Thank you soooooooooooo much sktneer!!!!!!

[sktneer]

Glad to help you. Thanks for the feedback.
If that takes care of your question, please mark your thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

[Dottiehelp]

Done....thanks for the tips on that as well!

[sktneer]

Thanks. You're welcome.