Strip Alpha Characters out of an Alphanumeric Dataset

[supersonicf111]

I am looking for a macro to Strip Alpha Characters out of an
Alphanumeric Dataset and leave me with just the Alpha characters.


De954 becomes just De

Thanks in advance for your help!

 

[Tom Ogilvy]

Use the replace function

Replace 0 with nothing
then 1 with nothing
etc.

 

[shockley]

Sub Tester()
sResult = fAlphaOnly("86xxxV86De954")
Stop
End Sub
Function fAlphaOnly(sTest)
For i = 1 To Len(sTest)
s = Mid(sTest, i, 1)
If Not s Like "#" Then _
s1 = s1 & s
Next i
fAlphaOnly = s1
End Function

HTH,
Shockley

 

[supersonicf111]

Tom,
Thanks for the help. Here is more detail of what what I am trying to
do. I have an inventory listing that has each item and a date code.
Prior to the year 2000 a date code may start with a 9 to identify that
it was produced in 1999 and any following digits are month and day. In
2000, they changed the year identifier to Alph Characters. So "A" now
signifies it was produced in 2000, "B" in 2001, and so forth. I found a
macro to strip the Alpha character off, but I need one to strip off the
nemeric characters so I am calculate each items age. I can't use
replace because it will alter the ones that don't have an alpha
character see examples below.

Item Date Code
1 90112 =January 12, 1999
2 A0520 =May 20, 2000
3 B1215 =December 15, 2001
4 80925 =September 25, 1998

 

[Gord Dibben]

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Gord Dibben Excel MVP

 

[supersonicf111]

Gord,
Thanks for the help. That did exactly what I wanted.
Bret

 

[Tom Ogilvy]

Do you want to strip the character off or do you want to create a valid date
from the information in the code.

Sub Tester5()
Dim cell As Range
Dim dtVal As Date
Dim sStr As String
For Each cell In Selection
If IsNumeric(Left(cell.Value, 1)) Then
sStr = Mid(cell.Value, 2, 2) & "/" & _
Right(cell.Value, 2) & "/" & "199" & Left(cell.Value, 1)
Else
sStr = Mid(cell.Value, 2, 2) & "/" & _
Right(cell.Value, 2) & "/" & "200"
sStr = sStr & (Asc(Left(cell.Value, 1)) - 65)
End If

dtVal = CDate(sStr)
Cells(cell.Row, "E").Value = Format(dtVal, "mmm dd, yyyy")

Next
End Sub



This give you the date. Tested with you codes in column A, placing the date
in column E.

 

[supersonicf111]

Tom,
When I try your post I get Run-time Error 5

It's no liking this line.

sStr = sStr & (Asc(Left(cell.Value, 1)) - 65)


Brett

 

[supersonicf111]

Tom,
When I try your post I get Run-time Error 5

It's no liking this line.

sStr = sStr & (Asc(Left(cell.Value, 1)) - 65)


Bret

 

[Tom Ogilvy]

It works fine for me with the test data you showed. But if Gord's does
what you wanted, then mine would not be useful. Mine actually builds the
dates as you described the codes.

 

[supersonicf111]

Tom,

Thanks for the help, yours actually makes for less work. I figured out
my problem. I had some blanks and the macro didn't like them. Once i
corrected that issue is ran fine.

Thanks again,
Brett

 

[Greg Rivet]

Gord and Tom, I have the same situation, however my alphanumeric range does
not refer to dates. I would like to do the opposite with range, that is keep
the numbers and remove the alpha characters. How do I modify the code to
accomplish this. TIA

Greg

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Gord Dibben Excel MVP

I am looking for a macro to Strip Alpha Characters out of an
Alphanumeric Dataset and leave me with just the Alpha characters.


De954 becomes just De

Thanks in advance for your help!

 

[Tom Ogilvy]

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If isnumeric(Mid(rngR.Value, intI, 1)) Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub
--
Regards,
Tom Ogilvy

Gord and Tom, I have the same situation, however my alphanumeric range does
not refer to dates. I would like to do the opposite with range, that is keep
the numbers and remove the alpha characters. How do I modify the code to
accomplish this. TIA

Greg

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Gord Dibben Excel MVP

I am looking for a macro to Strip Alpha Characters out of an
Alphanumeric Dataset and leave me with just the Alpha characters.


De954 becomes just De

Thanks in advance for your help!

 

[Greg Rivet]

Tom, thank you very much and have a very Happy New Year.

Greg

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If isnumeric(Mid(rngR.Value, intI, 1)) Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub
--
Regards,
Tom Ogilvy

Gord and Tom, I have the same situation, however my alphanumeric range does
not refer to dates. I would like to do the opposite with range, that is keep
the numbers and remove the alpha characters. How do I modify the code to
accomplish this. TIA

Greg

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Gord Dibben Excel MVP


On Tue, 30 Dec 2003 12:52:48 -0600, supersonicf111

I am looking for a macro to Strip Alpha Characters out of an
Alphanumeric Dataset and leave me with just the Alpha characters.


De954 becomes just De

Thanks in advance for your help!

 

[Dana DeLouis]

Here is just another idea if you would like to try something different. Set
a vba library reference to "Microsoft VBScript Regular Expressions 5.5" Use
"\D" to keep only numbers. Change it to "\d" to remove numeric digits.

keep the numbers and remove the alpha characters

Option Explicit
Dim Re As RegExp

Function NumbersOnly(s As String) As Variant
' = = = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Microsoft VBScript Regular Expressions 5.5
'// Removes Alpha characters, keeping just numbers.
' = = = = = = = = = = = = = = = = = = = = = = = = =
If Re Is Nothing Then
Set Re = New RegExp
Re.IgnoreCase = True
Re.Global = True
Re.Pattern = "\D" 'Non-Digit
End If

NumbersOnly = Re.Replace(s, vbNullString)
End Function

Sub TestIt()
Dim cell As Range

[A1] = "abc12cde34fg56h"
[A2] = "543abc210def"
[A3] = "ab987cd654"

Set Re = Nothing 'Reset before long loop
For Each cell In [A1:A3].Cells
cell = NumbersOnly(cell.Value)
Next
End Sub


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

Gord and Tom, I have the same situation, however my alphanumeric range does
not refer to dates. I would like to do the opposite with range, that is keep
the numbers and remove the alpha characters. How do I modify the code to
accomplish this. TIA

Greg

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Gord Dibben Excel MVP

I am looking for a macro to Strip Alpha Characters out of an
Alphanumeric Dataset and leave me with just the Alpha characters.


De954 becomes just De

Thanks in advance for your help!

 

[Andy Winter]

Sub CellSort() ' If you change the = to <> for the value of e The erro
value you can strip out either text or numbers
Set sourceCell = Worksheets("Sheet1").Range("G7") ' Start of lis
of cells to be split
Set destCell = Worksheets("Sheet1").Range("H7") ' start of list o
where result is to be placed



Do While Not IsEmpty(sourceCell)
l = Len(sourceCell)

For i = 1 To l
On Error GoTo ErrorHandler
c = Mid(sourceCell, i, 1)

c = c / 1

If e = 13 Then
destCell.Value = destCell.Value & c
End If

ErrorHandler:

e = Err.Number
Resume Next
Next i

Set sourceCell = sourceCell.Offset(1, 0)
Set destCell = destCell.Offset(1, 0)

Loop


End Su

 

[Greg Rivet]

Dana, thank you for your suggestion. I now have a need to split the digits
from alphas. Could we modify the code to split the two. TIA

Greg

Here is just another idea if you would like to try something different. Set
a vba library reference to "Microsoft VBScript Regular Expressions 5.5" Use
"\D" to keep only numbers. Change it to "\d" to remove numeric digits.

keep the numbers and remove the alpha characters

Option Explicit
Dim Re As RegExp

Function NumbersOnly(s As String) As Variant
' = = = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Microsoft VBScript Regular Expressions 5.5
'// Removes Alpha characters, keeping just numbers.
' = = = = = = = = = = = = = = = = = = = = = = = = =
If Re Is Nothing Then
Set Re = New RegExp
Re.IgnoreCase = True
Re.Global = True
Re.Pattern = "\D" 'Non-Digit
End If

NumbersOnly = Re.Replace(s, vbNullString)
End Function

Sub TestIt()
Dim cell As Range

[A1] = "abc12cde34fg56h"
[A2] = "543abc210def"
[A3] = "ab987cd654"

Set Re = Nothing 'Reset before long loop
For Each cell In [A1:A3].Cells
cell = NumbersOnly(cell.Value)
Next
End Sub


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

Gord and Tom, I have the same situation, however my alphanumeric range does
not refer to dates. I would like to do the opposite with range, that is keep
the numbers and remove the alpha characters. How do I modify the code to
accomplish this. TIA

Greg

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Gord Dibben Excel MVP


On Tue, 30 Dec 2003 12:52:48 -0600, supersonicf111

I am looking for a macro to Strip Alpha Characters out of an
Alphanumeric Dataset and leave me with just the Alpha characters.


De954 becomes just De

Thanks in advance for your help!

 

[Dana DeLouis]

Hello. I'm afraid I don't understand the question. Could you give a few
examples.

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

Dana, thank you for your suggestion. I now have a need to split the digits
from alphas. Could we modify the code to split the two. TIA

<snip>

 

[Greg Rivet]

Dana in other words A1 = AS154WEP7548WE would result in B1 = ASWEPWE and C1
= 1547548. TIA

Greg

 

[Dana DeLouis]

Would something like this work? Sometimes these can be a hair slower, but I
find them to be easier to use.

Option Explicit
Dim Re As RegExp

Function NumbersOnly(s As String) As Variant
' = = = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Microsoft VBScript Regular Expressions 5.5
'// Keep Only Numbers
' = = = = = = = = = = = = = = = = = = = = = = = = =
If Re Is Nothing Then
Set Re = New RegExp
Re.IgnoreCase = True
Re.Global = True
End If
Re.Pattern = "\D" 'Non-Digit
NumbersOnly = Re.Replace(s, vbNullString)
End Function

Function LettersOnly(s As String) As Variant
' = = = = = = = = = = = = = = = = = = = = = = = = =
'// Dana DeLouis
'// Microsoft VBScript Regular Expressions 5.5
'// Remove Numbers
' = = = = = = = = = = = = = = = = = = = = = = = = =
If Re Is Nothing Then
Set Re = New RegExp
Re.IgnoreCase = True
Re.Global = True
End If
Re.Pattern = "\d" 'Digits only
LettersOnly = Re.Replace(s, vbNullString)
End Function

Sub TestIt()
[A1] = "AS154WEP7548WE"
With [A1]
[B1:C1] = Array(LettersOnly(.Value), NumbersOnly(.Value))
End With
End Sub

Puts ASWEPWE in B1 and 1547548 in C1