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

User · Accepted Answer

What you need to do is as follows    List item Select the entire column by clicking once on the corresponding letter or by simply selecting the cells with your mouse  Press Ctrl H  You are now in the  Find and Replace  dialog  Write  Author  in the  Find what  text box  Write  Authoring  in the  Replace with  text box  Click the  Replace All  button    That s it

User · Answer

You have a character   STQ8QGpaM4CU6149665 7084880820  and you have a another column   7084880820    If you want to get only this in excel using the formula  STQ8QGpaM4CU6149665   use this     REPLACE H11 SEARCH J11 H11  LEN J11        H11 is an old character and for starting number use search option then for no of character needs to replace use len option then replace to new character  I am replacing this to blank

User · Answer

I know this is old but I had a similar need for this and I did not want to do the find and replace version  It turns out that you can nest the substitute method like so    SUBSTITUTE SUBSTITUTE F149   a     AM     p     PM     In my case  I am using excel to view a DBF file and however it was populated has times like this   9 16a 2 22p   So I just made a new column and put that formula in it to convert it to the excel time format

User · Answer

what you re looking for is SUBSTITUTE    SUBSTITUTE A2  Author   Authoring     Will substitute Author for Authoring without messing with everything else

[excel] Excel: replace part of cell's string value

Examples related to excel