I have a set of Alphanumeric data in Col D
I need to move only the numeric data to Col A
and then the Alpha data to Col B . The numeric data is variable so the
Data to Columns does not fit...
Example:
A B C D

So far can move the numeric data but stuck on the syntax for moving Alpha
Just getting started in Excel so would appreciate any help. thanx in Advance

Maybe this

Right click your sheet tab, view code and paste this in and run it

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim MyRange As Range, c As Range, Outstring As String
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
Else
.Pattern = "\d"
End If
End With
Set MyRange = ActiveSheet.Range("d1:D" & lastrow)
For Each c In MyRange
Outstring = ""
Set Collection = RegExp.Execute(c.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
c.Offset(0, -(x + 1)) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set MyRange = Nothing
Next
End Sub


Mike

"pattlee" wrote:

I have a set of Alphanumeric data in Col D
I need to move only the numeric data to Col A
and then the Alpha data to Col B . The numeric data is variable so the
Data to Columns does not fit...
Example:
A B C D

So far can move the numeric data but stuck on the syntax for moving Alpha
Just getting started in Excel so would appreciate any help. thanx in Advance

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt
"Mike H" wrote:

Maybe this

Right click your sheet tab, view code and paste this in and run it

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim MyRange As Range, c As Range, Outstring As String
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
Else
.Pattern = "\d"
End If
End With
Set MyRange = ActiveSheet.Range("d1:D" & lastrow)
For Each c In MyRange
Outstring = ""
Set Collection = RegExp.Execute(c.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
c.Offset(0, -(x + 1)) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set MyRange = Nothing
Next
End Sub


Mike

"pattlee" wrote:

I have a set of Alphanumeric data in Col D
I need to move only the numeric data to Col A
and then the Alpha data to Col B . The numeric data is variable so the
Data to Columns does not fit...
Example:
A B C D

So far can move the numeric data but stuck on the syntax for moving Alpha
Just getting started in Excel so would appreciate any help. thanx in Advance

"pattlee" wrote:


Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt.... OOPs reran the code correctly and everything is excellent......thanks so much This is wonderful..
"Mike H" wrote:

Maybe this

Right click your sheet tab, view code and paste this in and run it

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim MyRange As Range, c As Range, Outstring As String
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
Else
.Pattern = "\d"
End If
End With
Set MyRange = ActiveSheet.Range("d1:D" & lastrow)
For Each c In MyRange
Outstring = ""
Set Collection = RegExp.Execute(c.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
c.Offset(0, -(x + 1)) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set MyRange = Nothing
Next
End Sub


Mike

"pattlee" wrote:

I have a set of Alphanumeric data in Col D
I need to move only the numeric data to Col A
and then the Alpha data to Col B . The numeric data is variable so the
Data to Columns does not fit...
Example:
A B C D

So far can move the numeric data but stuck on the syntax for moving Alpha
Just getting started in Excel so would appreciate any help. thanx in Advance

I'm glad that's wonderful and thanks for the feedback.

Mike

"pattlee" wrote:



"pattlee" wrote:


Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt.... OOPs reran the code correctly and everything is excellent......thanks so much This is wonderful..
"Mike H" wrote:

Maybe this

Right click your sheet tab, view code and paste this in and run it

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim MyRange As Range, c As Range, Outstring As String
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
Else
.Pattern = "\d"
End If
End With
Set MyRange = ActiveSheet.Range("d1:D" & lastrow)
For Each c In MyRange
Outstring = ""
Set Collection = RegExp.Execute(c.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
c.Offset(0, -(x + 1)) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set MyRange = Nothing
Next
End Sub


Mike

"pattlee" wrote:

I have a set of Alphanumeric data in Col D
I need to move only the numeric data to Col A
and then the Alpha data to Col B . The numeric data is variable so the
Data to Columns does not fit...
Example:
A B C D

So far can move the numeric data but stuck on the syntax for moving Alpha
Just getting started in Excel so would appreciate any help. thanx in Advance

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

Thanks for the rapid response I am 75 and just learing Excel so thanks again
for helping out!... I am sure This code can be used again and again.... I
needed to learn syntax so this is wonderful.... Regards Patt

"Gord Dibben" wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

Start storing all these things......User Defined Fumctions and macros in
Personal.xls or store them in a new workbook and save it as an Add-in.

Either way, you will have them available for all open workbooks.

Us elderly gents have to stick together.


Gord

On Sun, 18 May 2008 16:21:02 -0700, pattlee
wrote:

Thanks for the rapid response I am 75 and just learing Excel so thanks again
for helping out!... I am sure This code can be used again and again.... I
needed to learn syntax so this is wonderful.... Regards Patt

"Gord Dibben" wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

That is exactly what I am doing... Storing all UDFs and macros in
Personal.xls. thanks for tip to save as new workbook and save as an
Add-in..it is so NICE when things work...You folks are really talented many
thanks for sharing your knowledge with us Newbies.... (us elderly ladies
admire such respect from elderly gents )

"Gord Dibben" wrote:

Start storing all these things......User Defined Fumctions and macros in
Personal.xls or store them in a new workbook and save it as an Add-in.

Either way, you will have them available for all open workbooks.

Us elderly gents have to stick together.


Gord

On Sun, 18 May 2008 16:21:02 -0700, pattlee
wrote:

Thanks for the rapid response I am 75 and just learing Excel so thanks again
for helping out!... I am sure This code can be used again and again.... I
needed to learn syntax so this is wonderful.... Regards Patt

"Gord Dibben" wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

My face is red.

Apologies for referring to you as a "gent"


Gord

On Sun, 18 May 2008 16:48:20 -0700, pattlee
wrote:

That is exactly what I am doing... Storing all UDFs and macros in
Personal.xls. thanks for tip to save as new workbook and save as an
Add-in..it is so NICE when things work...You folks are really talented many
thanks for sharing your knowledge with us Newbies.... (us elderly ladies
admire such respect from elderly gents )

"Gord Dibben" wrote:

Start storing all these things......User Defined Fumctions and macros in
Personal.xls or store them in a new workbook and save it as an Add-in.

Either way, you will have them available for all open workbooks.

Us elderly gents have to stick together.


Gord

On Sun, 18 May 2008 16:21:02 -0700, pattlee
wrote:

Thanks for the rapid response I am 75 and just learing Excel so thanks again
for helping out!... I am sure This code can be used again and again.... I
needed to learn syntax so this is wonderful.... Regards Patt

"Gord Dibben" wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

No way to know so no problem... no offense... thanks for your help and I will
be back with other requests. Regards Patt

"Gord Dibben" wrote:

My face is red.

Apologies for referring to you as a "gent"


Gord

On Sun, 18 May 2008 16:48:20 -0700, pattlee
wrote:

That is exactly what I am doing... Storing all UDFs and macros in
Personal.xls. thanks for tip to save as new workbook and save as an
Add-in..it is so NICE when things work...You folks are really talented many
thanks for sharing your knowledge with us Newbies.... (us elderly ladies
admire such respect from elderly gents )

"Gord Dibben" wrote:

Start storing all these things......User Defined Fumctions and macros in
Personal.xls or store them in a new workbook and save it as an Add-in.

Either way, you will have them available for all open workbooks.

Us elderly gents have to stick together.


Gord

On Sun, 18 May 2008 16:21:02 -0700, pattlee
wrote:

Thanks for the rapid response I am 75 and just learing Excel so thanks again
for helping out!... I am sure This code can be used again and again.... I
needed to learn syntax so this is wonderful.... Regards Patt

"Gord Dibben" wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

On Sun, 18 May 2008 16:02:10 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

This isn't any faster than your RemAlpha routine (probably slower) but for
consistency with the RemDigits function:

It probably should be called: RemNonDigits, though.

================================
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function
===============================

Or you could make it more generalized to Remove whatever is specified in the
Pattern:

==============================
Function RemSpec(str As String, sPattern As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern
RemSpec = re.Replace(str, "")
End Function
===============================

So, for sPattern (what will be removed)

"\d" = digits
"\D" = non-digits
"[A-Za-z]" = all letters
and there are many other patterns that could be used.
--ron

Ron.. thank you for this additonal solution.. showed code to another Excel
Newbie and concurred that these are invaluable functions for the project we
are involved in..this cleared up questions about how to move other data
thanks to you and Gord for your kinowledge and expert help. regards Patt

"Ron Rosenfeld" wrote:

On Sun, 18 May 2008 16:02:10 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

This isn't any faster than your RemAlpha routine (probably slower) but for
consistency with the RemDigits function:

It probably should be called: RemNonDigits, though.

================================
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function
===============================

Or you could make it more generalized to Remove whatever is specified in the
Pattern:

==============================
Function RemSpec(str As String, sPattern As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern
RemSpec = re.Replace(str, "")
End Function
===============================

So, for sPattern (what will be removed)

"\d" = digits
"\D" = non-digits
"[A-Za-z]" = all letters
and there are many other patterns that could be used.
--ron

On Mon, 19 May 2008 07:22:58 -0400, Ron Rosenfeld
wrote:

Thanks Ron.

Always willing to adjust..............although Mrs. D may not agree with that<g


Gord

This isn't any faster than your RemAlpha routine (probably slower) but for
consistency with the RemDigits function:

It probably should be called: RemNonDigits, though.

================================
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function
===============================

Or you could make it more generalized to Remove whatever is specified in the
Pattern:

==============================
Function RemSpec(str As String, sPattern As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern
RemSpec = re.Replace(str, "")
End Function
===============================

So, for sPattern (what will be removed)

"\d" = digits
"\D" = non-digits
"[A-Za-z]" = all letters
and there are many other patterns that could be used.
--ron

On Mon, 19 May 2008 06:33:02 -0700, pattlee
wrote:

Ron.. thank you for this additonal solution.. showed code to another Excel
Newbie and concurred that these are invaluable functions for the project we
are involved in..this cleared up questions about how to move other data
thanks to you and Gord for your kinowledge and expert help. regards Patt

Glad to help.

And if you do a Google search for "Regular Expressions", you can see how the
/d, /D and other expressions can be constructed, and find all kinds of use for
this routine. There is much else available also in this area of text
manipulation. Look here also:


http://msdn.microsoft.com/en-us/library/6wzad2b2.aspx
--ron

newbee to vbscript

could pls explain me how to use the same in my workbook.

Thanks
Rupesh

"Ron Rosenfeld" wrote:

On Sun, 18 May 2008 16:02:10 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

This isn't any faster than your RemAlpha routine (probably slower) but for
consistency with the RemDigits function:

It probably should be called: RemNonDigits, though.

================================
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function
===============================

Or you could make it more generalized to Remove whatever is specified in the
Pattern:

==============================
Function RemSpec(str As String, sPattern As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern
RemSpec = re.Replace(str, "")
End Function
===============================

So, for sPattern (what will be removed)

"\d" = digits
"\D" = non-digits
"[A-Za-z]" = all letters
and there are many other patterns that could be used.
--ron

Decide which of the UDF's you want to use.

I would suggest these two................................

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function

Alt + F11 to open VBEditor

Ctrl + r to open Project Explorer.

Select your workbook/project and right-clickinsert module

Copy/paste the Functions into the module.

FileSave the workbook.

Alt + F11 to return to the Excel window.

In a cell type =RemAlphas(cellref)


Gord

On Tue, 8 Jul 2008 10:08:13 -0700, Rupesh
wrote:

newbee to vbscript

could pls explain me how to use the same in my workbook.

Thanks
Rupesh

"Ron Rosenfeld" wrote:

On Sun, 18 May 2008 16:02:10 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

This isn't any faster than your RemAlpha routine (probably slower) but for
consistency with the RemDigits function:

It probably should be called: RemNonDigits, though.

================================
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function
===============================

Or you could make it more generalized to Remove whatever is specified in the
Pattern:

==============================
Function RemSpec(str As String, sPattern As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern
RemSpec = re.Replace(str, "")
End Function
===============================

So, for sPattern (what will be removed)

"\d" = digits
"\D" = non-digits
"[A-Za-z]" = all letters
and there are many other patterns that could be used.
--ron

Thanks for Prompt reply

"Gord Dibben" wrote:

Decide which of the UDF's you want to use.

I would suggest these two................................

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function

Alt + F11 to open VBEditor

Ctrl + r to open Project Explorer.

Select your workbook/project and right-clickinsert module

Copy/paste the Functions into the module.

FileSave the workbook.

Alt + F11 to return to the Excel window.

In a cell type =RemAlphas(cellref)


Gord

On Tue, 8 Jul 2008 10:08:13 -0700, Rupesh
wrote:

newbee to vbscript

could pls explain me how to use the same in my workbook.

Thanks
Rupesh

"Ron Rosenfeld" wrote:

On Sun, 18 May 2008 16:02:10 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Copy/paste these two functions to a general module in your workbook.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

Function RemDigits(str As String) As String
'Remove numbers from a string Ron Rosenfield Mar 20, 08
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

In A1 enter =remalphas(D1)

In B1 enter =remdigits(D1)

Copy down as far as you need.


Gord Dibben MS Excel MVP

On Sun, 18 May 2008 13:21:01 -0700, pattlee
wrote:

Hi Mike.. Ran the code and got "Compile Error" Variable not defined for
lastrow = Cells etc.... Please advise thanks Patt

This isn't any faster than your RemAlpha routine (probably slower) but for
consistency with the RemDigits function:

It probably should be called: RemNonDigits, though.

================================
Function RemAlpha(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function
===============================

Or you could make it more generalized to Remove whatever is specified in the
Pattern:

==============================
Function RemSpec(str As String, sPattern As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern
RemSpec = re.Replace(str, "")
End Function
===============================

So, for sPattern (what will be removed)

"\d" = digits
"\D" = non-digits
"[A-Za-z]" = all letters
and there are many other patterns that could be used.
--ron