Solved: Replace specified characters with a number

[X10A]

Hi,

The title may be a bit confusing but I have no idea how to better phrase it. What I am looking for is to replace all characters from "c to g" with the number 9.
e.g:
cat > 9at
Cat > Cat
egg > 999
Egg > E99

The only way I know is to use find and replace function. Is there a vba function something like this?

Replace lowercase [c-g] with "9"

I know Excel does not interpret human language, but I do not know how to write the code in VBA language.

Thanks for any help here.

[Bob Phillips]

Dim RegEx As Object
Dim cell As Range

For Each cell In Selection

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "[c-g]"
RegEx.Global = True
cell.Value = RegEx.Replace(cell.Value, "9")
Next cell
Set RegEx = Nothing

[X10A]

Ok guys, I found the solution. Ha, never thought I'll answer my own question.

Here is it:

Sub ReplaceChars()
' This will replace all case sensitive characters "c-g" to "9"
Dim TmpWd As String
TmpWd = ActiveCell.Value
For i = 1 To Len(TmpWd)
Select Case Mid(TmpWd, i, 1)
Case "c" To "g"
Mid(TmpWd, i, 1) = 9
End Select
Next i
ActiveCell = TmpWd
End Sub

[X10A]

Hi xld,

Thanks for the help! I didn't see your reply while I was posting the solution.

I just tried your code and it is a lot faster than mine! Thanks again.

[GTO]

ACK! Slow fingers...

[X10A]

Hi GTO,

The range is quite large and I see that we have the same thinking; looping through each character in each cell. :-)

I have already ran the code before I saw xld's solution. :-(
Anyway, I'm not in a rush so I can let the PC run the code while I have my coffee!

Thanks again!

[X10A]

Hi GTO,

That was a nice piece of code you have written too. I'm going to mark the thread as solved now. Again, thank you for your help!