CODEWITHSUNDEEP
excel
QMKevin
QMKevin

Reputation: 1111

Excel: replace part of cell's string value

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?

Upvotes: 26

Views: 218395

Answers (4)

Gokul
Gokul

Reputation: 1

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.

Upvotes: -2

Soenhay
Soenhay

Reputation: 4048

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.

Upvotes: 9

shaylh
shaylh

Reputation: 1859

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!

Upvotes: 31

DarkAjax
DarkAjax

Reputation: 16223

what you're looking for is SUBSTITUTE:

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

Will substitute Author for Authoring without messing with everything else

Upvotes: 73

Related Questions