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?
This question is related to
excel
what you're looking for is SUBSTITUTE:
=SUBSTITUTE(A2,"Author","Authoring")
Will substitute Author for Authoring without messing with everything else
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.
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.
Source: Stackoverflow.com