Removing Digits

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

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!
 
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
 
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!
Click to expand...

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
 
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
 
Back
Top