Excel XP: Treat specific text as zero.

[bigjayb024]

Essentially what the title says.

I have 2 specific words in my workbook that I need Excel to recognize as a 0, but display the entered text.

Normally I would just use 0, but in this case, 0 can actually mean 2 different things (but still calculate as 0).

The 2 words are "pick" and "off". I know I could change the formulas in other cells that reference these cells and use the IF function, but that would require a lot of time. The simpler method for me would be a VBA script.

Any help would be appreciated.

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[bigjayb024]

Sorry, I thought I made myself clear on what needed to be done. My mistake.

Here is an example.

VBA function would be preferred.

Thanks!

[:) Sixthsense :)]

No need for VBA because it can be easily achieved by correcting the formula.

In H12 Cell

=H9-IF(OR(H8={"Pick","Off"}),0,H8)


In J12 Cell

=J9-IF(OR(J8={"Pick","Off"}),0,J8)

[bigjayb024]

Ok, I guess I'll have to use this. I was trying to avoid changing the formulas because the workbook I was going to apply the VBA code to is more complicated than the simple formula in the example I used.

[ayreslandscapesvcs]

Thanks for sharing this I also have the same problem.