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!


---
Message posted from http://www.ExcelForum.com/

Use the replace function

Replace 0 with nothing
then 1 with nothing
etc.

--
Regards,
Tom Ogilvy

"supersonicf111" wrote in
message ...
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!


---
Message posted from http://www.ExcelForum.com/

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" wrote in
message ...
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!


---
Message posted from http://www.ExcelForum.com/

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


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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


---
Message posted from http://www.ExcelForum.com/

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.

--
regards,
Tom Ogilvy

"supersonicf111" wrote in
message ...
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


---
Message posted from http://www.ExcelForum.com/

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.

--
Regards,
Tom Ogilvy




"supersonicf111" wrote in
message ...
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


---
Message posted from http://www.ExcelForum.com/

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
wrote:

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!


---
Message posted from http://www.ExcelForum.com/

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
<Gord Dibben wrote in message
...
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
wrote:

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!


---
Message posted from http://www.ExcelForum.com/

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

"Greg Rivet" wrote in message
...
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
<Gord Dibben wrote in message
...
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
wrote:

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!


---
Message posted from http://www.ExcelForum.com/

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

Greg
"Tom Ogilvy" wrote in message
...
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

"Greg Rivet" wrote in message
...
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
<Gord Dibben wrote in message
...
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
wrote:

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!


---
Message posted from http://www.ExcelForum.com/

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
= = = = = = = = = = = = = = = = =


"Greg Rivet" wrote in message
...
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
<Gord Dibben wrote in message
...
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
wrote:

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!


---
Message posted from http://www.ExcelForum.com/

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
"Dana DeLouis" wrote in message
...
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
= = = = = = = = = = = = = = = = =


"Greg Rivet" wrote in message
...
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
<Gord Dibben wrote in message
...
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
wrote:

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!


---
Message posted from http://www.ExcelForum.com/

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

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


"Greg Rivet" wrote in message
...
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