Excel: replace part of cell's string value

Question

I have a large spreadsheet with a column called "Roles". In this columns are values like:

ROLES
Author
Author;
Publishing; Author;
Something Else; Author; Publishing

There are other columns where the word "Author" may exist.

What I need to do is look for "Author" in my "Roles" column only, and replace it with "Authoring", without losing anything before or after it. i.e. the end result should be:

ROLES
Authoring
Authoring;
Publishing; Authoring;
Something Else; Authoring; Publishing

I tried the FIND- and REPLACE-functions, but that replaced the entire cell value, not just a portion of it.

=IF(FIND("Author",[@Roles],1),REPLACE("Author",1,6,"Authoring"))

Can anyone help? I'd rather not us a VB solution, as I'm not familiar with how to do that, so hopefully there is a formula based way?

Answer 1

what you're looking for is SUBSTITUTE:

=SUBSTITUTE(A2,"Author","Authoring")

Will substitute Author for Authoring without messing with everything else

Answer 2

What you need to do is as follows:

1. List item
2. Select the entire column by clicking once on the corresponding letter or by simply selecting the cells with your mouse.
3. Press Ctrl+H.
4. You are now in the "Find and Replace" dialog. Write "Author" in the "Find what" text box.
5. Write "Authoring" in the "Replace with" text box.
6. Click the "Replace All" button.

That's it!