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?

DarkAjax · Accepted Answer · 2012-02-03 19:29:33Z

75

what you're looking for is SUBSTITUTE:

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

Will substitute Author for Authoring without messing with everything else

answered Feb 3, 2012 at 19:29

DarkAjax

16.3k11 gold badges60 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

QMKevin Over a year ago

It turns out that using shaylh's answer is better for what I need, but your answer is great too, and did exactly what I wanted, based on teh question. (what I didn't says is that I have multiple replacements to do, and the Ctrl+H version works best for that). Cheers :)

DarkAjax Over a year ago

Don't worry, if that's what you needed it's ok, at least now you'll have options in case you need something different

pratik garg Over a year ago

I was searching for one function name in excel to do the same working as 'Replace' in oracle.. well I was searching for a while and at the end, here I got my proper answer.

Mir Over a year ago

If, like me, you occasionally use excel to generate SQL statements... what you're looking for is [ SUBSTITUTE(A1,"'","''") ] to escape single quotes in the cell value, thus preventing garbage scripts!

shaylh · Accepted Answer · 2012-02-03 19:29:43Z

30

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!

answered Feb 3, 2012 at 19:29

shaylh

1,8573 gold badges17 silver badges20 bronze badges

4 Comments

QMKevin Over a year ago

Oh good grief! You're exactly right.. this is the best and quickest way to do this. I clearly did something wrong last time I tried that, as it was replacing values in other columns too, but your concise steps did the trick. Thanks :)

Nikolay Ivanov Over a year ago

This is not answer to the question asked! Look for SUBSTITUTE function from the answer from darkajax below. And should not be the accepted answer.

Nouman Bhatti Over a year ago

this is not working for me. whole cell content is replaced in my case

gkubed Over a year ago

This is working for me in Office 2010 Excel version 14.0.7214.5000.

Soenhay · Accepted Answer · 2013-03-04 23:07:47Z

10

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.

answered Mar 4, 2013 at 23:07

Soenhay

4,1086 gold badges39 silver badges62 bronze badges

Comments

Bhargav Rao · Accepted Answer · 2019-09-11 08:06:57Z

-2

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.

edited Sep 11, 2019 at 8:06

Bhargav Rao

52.6k29 gold badges130 silver badges142 bronze badges

answered Sep 11, 2019 at 7:05

Gokul

1

Collectives™ on Stack Overflow

Excel: replace part of cell's string value

4 Answers 4

4 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related