Strip Alpha Characters out of an Alphanumeric Dataset

  • Thread starter Thread starter supersonicf111
  • Start date Start date
I suppose you could combine them if you wanted into one function based on
what you are doing....

Option Explicit
Dim Re As RegExp

Function Letters_Numbers(s As String) As Variant
' = = = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Microsoft VBScript Regular Expressions 5.5
'// Return {Letters, Numbers}
' = = = = = = = = = = = = = = = = = = = = = = = = =
Dim v As Variant 'Hold temp variable

If Re Is Nothing Then
Set Re = New RegExp
Re.IgnoreCase = True
Re.Global = True
End If

Re.Pattern = "\d" 'Remove Digits
v = Re.Replace(s, vbNullString)
Re.Pattern = "\D" 'Remove Letters
Letters_Numbers = Array(v, Re.Replace(s, vbNullString))
End Function

Sub TestIt()
[A1] = "AS154WEP7548WE"
[B1:C1] = Letters_Numbers([A1].Value)
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

<snip>
 
Dana, works like a charm, but how do I call it. If I use
=Letters_Numbers(A1), I only get the letters not the numbers. If I run
"TestIt", it works as designed. TIA

Greg
 
Hi Greg. I was just throwing out some ideas. If you are using them on a
worksheet, you may want to separate the formulas into NumbersOnly and
LettersOnly. This might make the worksheet easier to read.
However, to answer your question, you would select B1:C1, type in
"=Letters_Numbers(A1)", and array enter the formula (Ctrl+Shift+Enter).
It would be similar to entering the Array formula like this...

Sub Demo()
[A1] = "AS154WEP7548WE"
[B1:C1].FormulaArray = "=Letters_Numbers(A1)"
End Sub


If you want in vba, you could use relative reference. Maybe something
like...

Sub Demo()
[A2] = "ABC123DEF456"
[B2:C2].FormulaArray = "=Letters_Numbers(RC[-1])"
End Sub

Just some ideas. HTH. :>)
In certain speed tests, I found this to be a "little" slower. However, I
like em.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Greg Rivet said:
Dana, works like a charm, but how do I call it. If I use
=Letters_Numbers(A1), I only get the letters not the numbers. If I run
"TestIt", it works as designed. TIA

Greg
Dana DeLouis said:
I suppose you could combine them if you wanted into one function based on
what you are doing....

Option Explicit
Dim Re As RegExp

Function Letters_Numbers(s As String) As Variant
' = = = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Microsoft VBScript Regular Expressions 5.5
'// Return {Letters, Numbers}
' = = = = = = = = = = = = = = = = = = = = = = = = =
Dim v As Variant 'Hold temp variable

If Re Is Nothing Then
Set Re = New RegExp
Re.IgnoreCase = True
Re.Global = True
End If

Re.Pattern = "\d" 'Remove Digits
v = Re.Replace(s, vbNullString)
Re.Pattern = "\D" 'Remove Letters
Letters_Numbers = Array(v, Re.Replace(s, vbNullString))
End Function

Sub TestIt()
[A1] = "AS154WEP7548WE"
[B1:C1] = Letters_Numbers([A1].Value)
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

<snip>
Click to expand...
Click to expand...
 
Back
Top