Move selected Text/Numbers from Col D to Col A/B

[pattlee]

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

 

[Mike H]

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" & 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]

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

 

[pattlee]

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]

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

Mike

 

[Gord Dibben]

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

 

[pattlee]

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

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

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

 

[Gord Dibben]

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

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

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

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

 

[pattlee]

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 )

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

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

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

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

 

[Gord Dibben]

My face is red.

Apologies for referring to you as a "gent"


Gord

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 )

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

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

:

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

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

 

[pattlee]

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

My face is red.

Apologies for referring to you as a "gent"


Gord

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 )

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

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

:

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

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

 

[Ron Rosenfeld]

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

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

 

[pattlee]

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

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

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

 

[Gord Dibben]

On Mon, 19 May 2008 07:22:58 -0400, Ron Rosenfeld <[email protected]>
wrote:

Thanks Ron.

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


Gord

 

[Ron Rosenfeld]

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

 

[Rupesh]

newbee to vbscript

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

Thanks
Rupesh

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

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

 

[Gord Dibben]

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-click>insert module

Copy/paste the Functions into the module.

File>Save the workbook.

Alt + F11 to return to the Excel window.

In a cell type =RemAlphas(cellref)


Gord

newbee to vbscript

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

Thanks
Rupesh

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

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

 

[Rupesh]

Thanks for Prompt reply

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-click>insert module

Copy/paste the Functions into the module.

File>Save the workbook.

Alt + F11 to return to the Excel window.

In a cell type =RemAlphas(cellref)


Gord

newbee to vbscript

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

Thanks
Rupesh

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

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