Removing Digits

[Faraz A. Qureshi]

Any formula to convert entries like

123AAA456BBB
AA12BB4567CCC

to:

AAABBB
AABBCCC

i.e. simply removing the digits 0-9 from the entry?

Thanx in advance!

 

[Jacob Skaria]

Hi Faraz, incase you are looking for a UDF

Function RemoveNums(strData As String) As String
Dim intTemp As Integer
For intTemp = 1 To Len(strData)
If Not IsNumeric(Mid(strData, intTemp, 1)) Then _
RemoveNums = RemoveNums & Mid(strData, intTemp, 1)
Next
End Function

If this post helps click Yes

 

[Bernd P]

Hello Faraz,

You will need a UDF for this, I think.

I suggest to use:
=RegExpReplace(A1,"\d","",FALSE,TRUE)

The function RegExpReplace you can find here:
http://sulprobil.com/html/regexp.html

Regards,
Bernd

 

[Ron Rosenfeld]

Any formula to convert entries like

123AAA456BBB
AA12BB4567CCC

to:

AAABBB
AABBCCC

i.e. simply removing the digits 0-9 from the entry?

Thanx in advance!

You can do this with a UDF.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=RemDigits(A1)

in some cell.

=============================
Option Explicit
Function RemDigits(s As String) As String
Dim re As Object
Const sPat As String = "\d+"

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
RemDigits = re.Replace(s, "")
End Function
===============================
--ron

 

[Jacob Skaria]

Hi Faraz

If you are looking for a formula solution you can try the below which uses a
helper cell. (Copied from a previous post)..Thought if this might help..

In cell A1 you have the text..
In cell B1 (helper column)
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,),2,),3,),4,),5,)

In cell C1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,6,),7,),8,),9,),0,)




If this post helps click Yes