hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

With a macro:-

Sub removeletters()
For Each cell In Selection
For a = 1 To Len(cell)
If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _
Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _
newstring = newstring & Mid(cell, a, 1)
Next
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next
End Sub

Select the cells to convert and run this

Mike

"peyman" wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

hi Mike,
it just removes dash "-" from string!! not letters.

"Mike H" wrote:

With a macro:-

Sub removeletters()
For Each cell In Selection
For a = 1 To Len(cell)
If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _
Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _
newstring = newstring & Mid(cell, a, 1)
Next
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next
End Sub

Select the cells to convert and run this

Mike

"peyman" wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

Another way.

I'm a bit cofused why the previous method only removed th dash but try this

Sub anotherway()
For Each cell In Selection
For a = 1 To Len(cell)
Select Case Mid(cell, a, 1)
Case "0" To "9"
newstring = newstring & Mid(cell, a, 1)
End Select
Next a
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next cell
End Sub

Mike

"peyman" wrote:

hi Mike,
it just removes dash "-" from string!! not letters.

"Mike H" wrote:

With a macro:-

Sub removeletters()
For Each cell In Selection
For a = 1 To Len(cell)
If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _
Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _
newstring = newstring & Mid(cell, a, 1)
Next
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next
End Sub

Select the cells to convert and run this

Mike

"peyman" wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

thanx mike,it works.I really appreciate it.


"Mike H" wrote:

Another way.

I'm a bit cofused why the previous method only removed th dash but try this

Sub anotherway()
For Each cell In Selection
For a = 1 To Len(cell)
Select Case Mid(cell, a, 1)
Case "0" To "9"
newstring = newstring & Mid(cell, a, 1)
End Select
Next a
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next cell
End Sub

Mike

"peyman" wrote:

hi Mike,
it just removes dash "-" from string!! not letters.

"Mike H" wrote:

With a macro:-

Sub removeletters()
For Each cell In Selection
For a = 1 To Len(cell)
If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _
Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _
newstring = newstring & Mid(cell, a, 1)
Next
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next
End Sub

Select the cells to convert and run this

Mike

"peyman" wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

hi Mike,
the problem is , it removes the zero at the begining of a numbers after
pulling out them,like:
aab0125 turns to 125 instead of 0125

"Mike H" wrote:

Another way.

I'm a bit cofused why the previous method only removed th dash but try this

Sub anotherway()
For Each cell In Selection
For a = 1 To Len(cell)
Select Case Mid(cell, a, 1)
Case "0" To "9"
newstring = newstring & Mid(cell, a, 1)
End Select
Next a
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next cell
End Sub

Mike

"peyman" wrote:

hi Mike,
it just removes dash "-" from string!! not letters.

"Mike H" wrote:

With a macro:-

Sub removeletters()
For Each cell In Selection
For a = 1 To Len(cell)
If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _
Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _
newstring = newstring & Mid(cell, a, 1)
Next
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next
End Sub

Select the cells to convert and run this

Mike

"peyman" wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

Hi,

It doesn't I suspect the zero is a letter "o" or "O"

Mike

"peyman" wrote:

hi Mike,
the problem is , it removes the zero at the begining of a numbers after
pulling out them,like:
aab0125 turns to 125 instead of 0125

"Mike H" wrote:

Another way.

I'm a bit cofused why the previous method only removed th dash but try this

Sub anotherway()
For Each cell In Selection
For a = 1 To Len(cell)
Select Case Mid(cell, a, 1)
Case "0" To "9"
newstring = newstring & Mid(cell, a, 1)
End Select
Next a
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next cell
End Sub

Mike

"peyman" wrote:

hi Mike,
it just removes dash "-" from string!! not letters.

"Mike H" wrote:

With a macro:-

Sub removeletters()
For Each cell In Selection
For a = 1 To Len(cell)
If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _
Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _
newstring = newstring & Mid(cell, a, 1)
Next
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next
End Sub

Select the cells to convert and run this

Mike

"peyman" wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

On Tue, 21 Aug 2007 11:18:02 -0700, Mike H
wrote:

With a macro:-

Sub removeletters()
For Each cell In Selection
For a = 1 To Len(cell)
If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _
Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _
newstring = newstring & Mid(cell, a, 1)
Next
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next
End Sub



Your For/Next loop can be simplified:


For a = 1 To Len(cell)
If Mid(cell, a, 1) Like "#" Then
newstring = newstring & Mid(cell, a, 1)
End If
Next


--ron

hi Ron,
the problem in Mikes macro is , it removes the zero at the begining of a
numbers after pulling out them,like:
aab0125 turns to 125 instead of 0125

"Ron Rosenfeld" wrote:

On Tue, 21 Aug 2007 11:18:02 -0700, Mike H
wrote:

With a macro:-

Sub removeletters()
For Each cell In Selection
For a = 1 To Len(cell)
If Asc(Mid(cell, a, 1)) 47 And Asc(Mid(cell, a, 1)) < 59 Or _
Asc(Mid(cell, a, 1)) 64 And Asc(Mid(cell, a, 1)) < 91 Then _
newstring = newstring & Mid(cell, a, 1)
Next
cell.Value = newstring
ActiveCell.NumberFormat = "General"
newstring = ""
Next
End Sub



Your For/Next loop can be simplified:


For a = 1 To Len(cell)
If Mid(cell, a, 1) Like "#" Then
newstring = newstring & Mid(cell, a, 1)
End If
Next


--ron

On Tue, 21 Aug 2007 10:28:00 -0700, peyman
wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

You can use a UDF.

To enter the UDF, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter =ExtrNums(cell_ref) into some cell.

===============================
Option Explicit

Function ExtrNums(str As String)
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\D"
ExtrNums = re.Replace(str, "")
End Function
================================


--ron

thank you so much.that's great!!!!!!!!!!

"Ron Rosenfeld" wrote:

On Tue, 21 Aug 2007 10:28:00 -0700, peyman
wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

You can use a UDF.

To enter the UDF, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter =ExtrNums(cell_ref) into some cell.

===============================
Option Explicit

Function ExtrNums(str As String)
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\D"
ExtrNums = re.Replace(str, "")
End Function
================================


--ron

You're welcome. Glad to help. Thanks for the feedback.

On Tue, 21 Aug 2007 11:32:00 -0700, peyman
wrote:

thank you so much.that's great!!!!!!!!!!

"Ron Rosenfeld" wrote:

On Tue, 21 Aug 2007 10:28:00 -0700, peyman
wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

You can use a UDF.

To enter the UDF, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter =ExtrNums(cell_ref) into some cell.

===============================
Option Explicit

Function ExtrNums(str As String)
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\D"
ExtrNums = re.Replace(str, "")
End Function
================================


--ron


--ron

Ron,

Excellent. any chance of a quick rundown on how it works or a web reference
please?

Mike H

"Ron Rosenfeld" wrote:

On Tue, 21 Aug 2007 10:28:00 -0700, peyman
wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

You can use a UDF.

To enter the UDF, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter =ExtrNums(cell_ref) into some cell.

===============================
Option Explicit

Function ExtrNums(str As String)
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\D"
ExtrNums = re.Replace(str, "")
End Function
================================


--ron

Mike,

It uses "Regular Expressions" which is a feature included with VBA.

The CreateObject method sets up the reference to Microsoft VBScript Regular
Expressions 5.5. Instead of that method, one can also select Tools/References
(from the VBEditor top menu) and select that as a reference. This latter
method has the advantage that properties will pop up when you are writing the
macro, as they do for other objects.

The Pattern "\D" means "match every character that is not a digit (not 0..9).
The replace method then looks at "str" and replaces every match with nothing
(""), thereby removing all the non-digits.

http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/1400241x.aspx



On Tue, 21 Aug 2007 11:36:00 -0700, Mike H
wrote:

Ron,

Excellent. any chance of a quick rundown on how it works or a web reference
please?

Mike H

"Ron Rosenfeld" wrote:

On Tue, 21 Aug 2007 10:28:00 -0700, peyman
wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

You can use a UDF.

To enter the UDF, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter =ExtrNums(cell_ref) into some cell.

===============================
Option Explicit

Function ExtrNums(str As String)
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\D"
ExtrNums = re.Replace(str, "")
End Function
================================


--ron


--ron

Thanks Ron,

I'm only on my 4th read of John Walkenbach's 2003 bible so perhaps some way
off this yet.

Mike

"Ron Rosenfeld" wrote:

Mike,

It uses "Regular Expressions" which is a feature included with VBA.

The CreateObject method sets up the reference to Microsoft VBScript Regular
Expressions 5.5. Instead of that method, one can also select Tools/References
(from the VBEditor top menu) and select that as a reference. This latter
method has the advantage that properties will pop up when you are writing the
macro, as they do for other objects.

The Pattern "\D" means "match every character that is not a digit (not 0..9).
The replace method then looks at "str" and replaces every match with nothing
(""), thereby removing all the non-digits.

http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/1400241x.aspx



On Tue, 21 Aug 2007 11:36:00 -0700, Mike H
wrote:

Ron,

Excellent. any chance of a quick rundown on how it works or a web reference
please?

Mike H

"Ron Rosenfeld" wrote:

On Tue, 21 Aug 2007 10:28:00 -0700, peyman
wrote:

hi,
how can I pull out numbers from a string?like:
aa012985 to 012985
12ab-059 to 12059
the letters or characters might be either at the first, middle or at the end
of a string.
thanx in advance.

You can use a UDF.

To enter the UDF, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter =ExtrNums(cell_ref) into some cell.

===============================
Option Explicit

Function ExtrNums(str As String)
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\D"
ExtrNums = re.Replace(str, "")
End Function
================================


--ron


--ron

On Tue, 21 Aug 2007 12:16:02 -0700, Mike H
wrote:

Thanks Ron,

I'm only on my 4th read of John Walkenbach's 2003 bible so perhaps some way
off this yet.

Mike

Regular expressions are extremely powerful tools for string manipulation.
Harlan Grove turned me on to them, and I've found them quite worthwhile,
although I'm still a novice in their use.
--ron