replacing number from the text

[lezawang]

Hi
What is the best way to get rid of numbers in the column below? I only want "John" name to appear without numbers. I can do Find/Replace, or use nested Substitute() but has to be nested. Is there better way to do that? Thank you very much

[TABLE="class: grid, width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]john1[/TD]
[/TR]
[TR]
[TD]john2[/TD]
[/TR]
[TR]
[TD]john3[/TD]
[/TR]
[TR]
[TD]john4[/TD]
[/TR]
[TR]
[TD]john5[/TD]
[/TR]
[TR]
[TD]john6[/TD]
[/TR]
[TR]
[TD]john7[/TD]
[/TR]
[TR]
[TD]john8[/TD]
[/TR]
[TR]
[TD]john9[/TD]
[/TR]
[TR]
[TD]john10[/TD]
[/TR]
[TR]
[TD]john11[/TD]
[/TR]
[TR]
[TD]john12[/TD]
[/TR]
[TR]
[TD]john13[/TD]
[/TR]
[TR]
[TD]john14[/TD]
[/TR]
[TR]
[TD]john15[/TD]
[/TR]
[TR]
[TD]john16[/TD]
[/TR]
[TR]
[TD]john17[/TD]
[/TR]
[TR]
[TD]john18[/TD]
[/TR]
[TR]
[TD]john19[/TD]
[/TR]
[TR]
[TD]john20[/TD]
[/TR]
[TR]
[TD]john21[/TD]
[/TR]
[TR]
[TD]john22[/TD]
[/TR]
[TR]
[TD]john23[/TD]
[/TR]
[TR]
[TD]john24[/TD]
[/TR]
[TR]
[TD]john25[/TD]
[/TR]
[TR]
[TD]john26[/TD]
[/TR]
[TR]
[TD]john27[/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

Hi lezawang,

You can use a UDF to replace any non alphabetical characters

Function REPLACENUM(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^a-zA-Z]"
        REPLACENUM = .Replace(txt, "")
    End With
End Function

 

[lezawang]

Thank you very much for your help. Could you please break the code down. I did not understand this part
.Pattern = "[^a-zA-Z]"

Also what is UDF? Thanks once again.

You can use a UDF to replace any non alphabetical characters

Function REPLACENUM(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^a-zA-Z]"
        REPLACENUM = .Replace(txt, "")
    End With
End Function

 

[tyija1995]

Thank you very much for your help. Could you please break the code down. I did not understand this part
.Pattern = "[^a-zA-Z]"

Also what is UDF? Thanks once again.

Yeah no worries, the "[^a-zA-Z]" part basically looks for any character in your string that isn't between a-z or A-Z. So e.g. "he11o w0rld" would pick up "11 0" (includes the space)
Then the code replaces all of this text with "" (blank) so in this instance "heowrld" would be the new string after calling the function.

A UDF is a User Defined Function, a function that is created by the user (your very own function if you will)

I hope this helps and solves the problem!

 

[lezawang]

Thank you so much. Appreciate your help