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

john1

john2

john3

john4

john5

john6

john7

john8

john9

john10

john11

john12

john13

john14

john15

john16

john17

john18

john19

john20

john21

john22

john23

john24

john25

john26

john27

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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