Replace text

[paintstripper]

I have a spreadsheet that has a list of 'codes' used to calculate prices.
EG
TOMATO
CHEESE
OLIVES

Because there are many instances of these names and more, I want to allow a user of the spreadsheet to enter in one of these codes and the formula to search for every instance of these codes, and replacing them with a character, which will make my VLOOKUP stop working, such as '^'.

The goal of this is instead of going through and changing 500 different instances of one code, just to see the resulting totals. I want to be able to replace this code with a character, so that the VLOOKUP does not work. Because the VLOOKUP has error checking, it will not give an error, just leave a blank cell.

I hope I have explained this well.

Dan

[davesexcel]

I don't know if you have tried autofilter, but here is a small example of how it works

[paintstripper]

I want to be able to do something similar, such as just find all the tomato entries and replace them with a character, such as '%'. How would I be able to do this?

Thanks,
Dan

[davesexcel]

Hi,
Do you want a symbol added , just to seperate the vlookup function?

[paintstripper]

For this purpose, the Vlookup function is not important.
All I need is to be able to search through a whole worksheet and replace the searched word with the symbol %. The purpose of this is the vlookup function will produce an error. Since I have error checking in my spreadsheet, the cell with the VLOOKUP function will be empty.

This will exclude it from a total. The entire purpose is to be able to remove one ingredient at will, then put it back later when the user says so.

Thanks,
Dan

[davesexcel]

Hit Ctrl f

to start

[paintstripper]

I can't do it like that. Here is exactly what I need.
Two cells, one for the text to search for. The other for the text to replace.
Then the function searches and replaces the text. So yes, it will be essentially a find/replace, the same as ctrl+f, but I need to be able to do it in the worksheet.

Thanks,
Dan

[Bryan Hessey]

I can't do it like that. Here is exactly what I need.
Two cells, one for the text to search for. The other for the text to replace.
Then the function searches and replaces the text. So yes, it will be essentially a find/replace, the same as ctrl+f, but I need to be able to do it in the worksheet.

Thanks,
Dan

Hi,

reading the above, why replace the 'Tomato' in the spreadsheet to cause the VLookup to fail, why not go to the lookup table and replace 'Tomato' with 'T^omato' which will cause all Lookup's on 'Tomato' to fail.

It would be easier to restore all products if required.

hth
---

[paintstripper]

I have to have the vlookup table protected, so that users can not change the codes. The users can however, change the other worksheet where the data is entered to be looked up. Is it even possible to do a code based search and replace for a single worksheet for all instances of a case sensitive word?

This is the simplest way that I can think to do it, but I do not know how to implement it. The word has to be case sensitive because there are other words being used as descriptions that I do not want to delete.

Thanks,
Dan

[Bryan Hessey]

I have to have the vlookup table protected, so that users can not change the codes. The users can however, change the other worksheet where the data is entered to be looked up. Is it even possible to do a code based search and replace for a single worksheet for all instances of a case sensitive word?

This is the simplest way that I can think to do it, but I do not know how to implement it. The word has to be case sensitive because there are other words being used as descriptions that I do not want to delete.

Thanks,
Dan

Indeed you have a strange sense of security when you protect 3 single words, TOMATO, CHEESE and OLIVES on one sheet from being changed, and then, on another sheet, think of allowing users to change all TOMATO to ^, change all CHEESE to ^, change all OLIVES to ^, and then try to set the words back to their original status.

The search & replace should be restricted to a single column, rather than try to rely on case sensitivity.

The best idea for you would be something like type the two items (Tomatoes and T^) into a pair of cells, then use shortcut keys to run a pre-recorded macro to do the Replace.

The macro could test that no T^ existed before running.

A Prettier idea would be a Menu selected small Userform, which permitted two entries and had a trigger button.

Try the recorded macro, (Tools, Macro, Record) and set the Shortcut keys, they really are useful in these type of cases.

hth
---