• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

special replace option not found

Harry0

Member
MS office has the ability to replace with special options, but can excel do that too?

What the wild card is the * or ?? but what if I want to keep a random number that it finds what do I use? *ignore*?

For example 2 lines that state
,IF(K4="","",VLOOKUP(K4,'Full List'!$A$2:$G$2000,4))
with
,VLOOKUP(K4,'Full List'!$A$2:$G$2000,4)
only the beginning and the end would be removed while the rest is kept.
,IF(K4="","".....................................)

Another twist would be to switch

vlookup(a1, b1:c2,1)
vlookup(a2, b1:c2,2)

I want the replace options to change it to be
index(b1:c2, a1,1
index(b1:c2, a2,2

It would be a combination of replace and offset.
I would need an excel formula within the excel replace command. LOL

Usually I do this with various methods but excel like to prevent me to do it my way and gives me errors. Another way would be a non excel approach which I do not want to mess with hex editors at the moment on a limited computer that can't handle it, or use old outdated programs that can handle it with a combination of other tools to exchange code between various outdated programs to do the job.
 
Hi ,

I am sorry but most of your posts seem to be 50% rant , and 50% something else ; if only you could just describe the problem you want solved , without bringing in VLOOKUP or any other Excel function , I am sure you can be more productive and probably more appreciative of Excel.

Narayan
 
MS word can somewhat do it.
In short can excel replace a formula in the middle of a cell that consists of multiple formulas across multiple cells?

Note if you remove a formula in the cell you also have to remove the ) at the end of the cell that is associated with it which excel won't let you replace one at a time since it gives an error.
 
Hi ,

Still no clarity ; in short , my answer would be maybe.

If you want a specific , precise answer , please post a specific , clear question.

Narayan
 
another example
How do you use the replace command ONLY to replace this in a formal
Here is a silly example
if(a1=1,1,if(a1=2,2,if(a1=3,3,4)))
To be
if(a1=1,1,if(a1=3,3,4))

So you would need to remove the if(a1=2,2, and the ) at the same time with the replace command.

that is clear so if excel can not do it then it can not do it.
 
Hi ,

Excel's Find and Replace is meant to work on text ; using it on formulae may or may not work , since formulae are subject to syntactic rules which general text is not.

However , if we treat the posted formula as text , we can replace the segment :

if(a1=1,1,if(a1=2,2,if(a1=3,3,4)))

with this :

IF(A1=3,3,4)

so that we end up with :

=IF(A1=1,1,IF(A1=3,3,4))

However , all this leads us nowhere ; there is no software on earth which can do everything that all its users want to do.

If we wish to be productive , and achieve something , we need to put aside our prejudices , and put our energies into learning and using ; the more the better.

Narayan
 
I gave a simple example to talk about the replace options.

My comments are not to attack excel but to ask if it can do it, or to think about how to fix it if someone is involved with it.

The things I want is consistancy and evolution as I explained in the other post since many things are not consistent and get worse which I can give you a long explanation with example but ill just say that.
 
Here is a way to do it which is good for if you want to change 100's of cells at once for a bit more advanced formulas than this obviously.
1 Replace (remove =)
=if(a1=1,1,if(a1=2,2,if(a1=3,3,4)))
with
if(a1=1,1,if(a1=2,2,if(a1=3,3,4)))

2 replace (remove the line * is for wildcard)
1,if(a1=*,*,
with
1,
3 Replace ( ) )
)))
with
))

4 Replace (add =)
if(a1=1
With
=if(a1=1

This seems to work for some things but not everything.
Another way to deal with excels limitations that should work for everything would take longer but to use excel to strip each formula into pieces onto another cells and just remove/add the column and then join them again.
 
Back
Top