• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

How do you replace data in selected cells in excel?

How do you replace data in selected cells in excel? What's the correct formula in excel (2007)?
0
Michael Sterling
Asked:
Michael Sterling
  • 9
  • 7
  • 6
  • +1
2 Solutions
 
SiddharthRoutCommented:
Have you checked the Replace() in Excel

From the Excel help file

REPLACE(old_text,start_num,num_chars,new_text)

Sid
0
 
GrahamSkanRetiredCommented:
This should do it:

    Selection.Replace What:="abc", Replacement:="def"
0
 
GrahamSkanRetiredCommented:
Note that the above is VBA macro language, not an Excel formula.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@Graham: yeh,... i need an actual excel formula

@Sidd: I've tried that, it only works for the first cell that I have highlighted. it doesn't seem to work for multiple selected cells. Any idea why? or how to make it work for when more than one cell is selected?
0
 
SiddharthRoutCommented:
For that you will have to use VBA .Find()

Here is a small tutorial I wrote on .Find()

http://www.vbforums.com/showthread.php?p=3922547

Sid
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@Sid: I appreciate the link, i really was looking to get this done in excel, using what's available to me in excel. is this possible?
0
 
SiddharthRoutCommented:
Hmm, With one formula replacing multiple cell is not possible. At least I am not aware of.

Sid
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@Sidd: ok, thanks it was just for a one time problem that i didn't want to have to touch each column for but looks like i'm gonna have to. No biggie, I may use your code, in the future, (written in C#: [shhh, i don't know VB very well...;-)] to build a utility for future instances and in case a bigger use for this comes up. Thanks again.
0
 
SiddharthRoutCommented:
It it is a one time thing then you can also use Ctrl + F to replace manually. ;)

Sid
0
 
SiddharthRoutCommented:
It is not possible is also a Valid Answer ;)
0
 
dlmilleCommented:
If you're trying to do this without a macro, just follow this direction:

select the cells you want to replace, then from the Home Ribbon, above, click the Binoculars on the far right, then REPLACE.

or, just hit ALT - EFR  then you can enter search/replace text.

Dave
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
always overlook the obvious...thanks dlmille. i was making it too complicated...i saw the cells and immediately starting thinking formula...
0
 
SiddharthRoutCommented:
Mike. How does that answer to your original question?

Sid
0
 
GrahamSkanRetiredCommented:
I'm not really into Excel formulae, but, as I understand the situation, it doesn't sound like the sort of thing that a formula can do
0
 
SiddharthRoutCommented:
You wanted an Excel Formula right?

>>>What's the correct formula in excel (2007)?

Sid
0
 
GrahamSkanRetiredCommented:
Oops. I see that is your conclusion as well.
0
 
dlmilleCommented:
Mike - I believe sid offered ctrl-F for manual replace as well ( I was sending at same time ) = recommend a points split at least!

Dave
0
 
SiddharthRoutCommented:
Thanks Dave. not required. :)

Sid
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@Sidd: My mistake, don't want to cheat or discount your efforts. I've sent a question to the moderator on how i can go about splitting the points.
0
 
SiddharthRoutCommented:
Thanks Ok Mike :)

Sid
0
 
GrahamSkanRetiredCommented:
Yes, it would be helpful, if you accept a solution other than the ones that you specifically asked for, to indicate why you are assigning credit to any particular solutions offered.
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@Graham & @Sidd: is it solved now?! never been through this process before...
0
 
GrahamSkanRetiredCommented:
You just carry on from here as if you were closing the question (by accepting one or more answers) for the first time.
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
sorry about the confusion, graham, if you feel that you are owed points too, let me know, it's no problem to re-do this...now that i know how
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 9
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now