• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • 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
 
GrahamSkanCommented:
This should do it:

    Selection.Replace What:="abc", Replacement:="def"
0
 
GrahamSkanCommented:
Note that the above is VBA macro language, not an Excel formula.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
GrahamSkanCommented:
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
 
GrahamSkanCommented:
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
 
GrahamSkanCommented:
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
 
GrahamSkanCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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