Macro to replace certain text

[bmoore45]

Hey guys

I'm looking for a way to delete any occurence of the letter 'o' in column G of my chart, does anyone know how I can do this?

Cheers,

ben

 

[wigi]

Hey guys

I'm looking for a way to delete any occurence of the letter 'o' in column G of my chart, does anyone know how I can do this?

Cheers,

ben

You mean, column G of the worksheet?

 

[Michael M]

Hi Ben
Have you considered simply using the Find / Replace function.
Highlight column "G" then
In the find line use "o" without the quotes
and in the replace line, leave it blank.

 

[bmoore45]

thanks yeah I mean the worksheet.


Michael:I'm using an ahk program to interact with the worksheet so clicking a hotkey to run a macro that would do this would be more efficient than entering data into the find/replace window imo.

Also I tried this method as it definitely would suffice but I seem to be getting an error. The cells contain formulas, I just want to replace the o's in the values not in the formula

 

[Michael M]

You could then simply record the macro using the macro recorder and the steps I have suggested.

 

[bmoore45]

Yeah and that would work fine, except the find and replace tool is the problem for me; if all I wanted to do was find the o's in cell values then that would be simple but in the replace tab the only option for Look in: is "Formulas", so I'm still not sure how to do it. Sorry to be a pain

 

[wigi]

What kind of formulas do you have in that column?

 

[bmoore45]

Just IF,NOT, and OR statements

 

[Rick Rothstein]

Yeah and that would work fine, except the find and replace tool is the problem for me; if all I wanted to do was find the o's in cell values then that would be simple but in the replace tab the only option for Look in: is "Formulas", so I'm still not sure how to do it. Sorry to be a pain

You can't have replace change the output from a formula... the output is the output. Just think if you could use Edit/Replace to do that... the next time the worksheet was recalculated, the replaced letter would just pop back in... that's what the formula outputs after all... and since the Replace operation is a manual "thing", it would not re-occur until you manually performed it again... I think you would get real tired trying to keep up with all the recalculations that take place in Excel. The way to handle this is to make the replacement take place as part of the formula...

=SUBSTITUTE([YourFormulaGoesHere]<YOURFORMULAGOESHERE><YOURFORMULAGOESHERE><YOURFORMULAGOESHERE>,"o","X")

where [YourFormulaGoesHere] would be the part after the equal sign, of course, and you would use whatever character or characters you want in place of the letter "o" instead of the "X" that I used. If you just wanted to remove the letters "o", then just use "" instead of "X".

 

[Rick Rothstein]

*** message remove as the change i thought i needed to make was not needed ***