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/

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,
Thanks for the help. That did exactly what I wanted.
Bret

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

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

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/

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/

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/

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

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

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

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

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

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


"Greg Rivet" wrote in message
...
Dana in other words A1 = AS154WEP7548WE would result in B1 = ASWEPWE and
C1
= 1547548. TIA

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

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

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