If you strings all start with digits than simply use VAL(Range("A1")) or if
there is spaces before the digits VAL(trim(Range("A1")))

"Mike H" wrote:


Use this pattern

.Pattern = "\d"

Mike
"Benjamin" wrote:

Mike:

Ah... caught one little bug here.
I've got Strings like this ....
1A
1
12A
12
120A
120
1201A
1201

I realized I didn't explain my situation well enough.
Could you help me tweak this to include, 1-4 digit number patterns, with
alpha characters after them that need to be removed. Otherwise, the code
works great.

"Mike H" wrote:

Benjamin,

Try this one

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim MyRange As Range, c As Range, Outstring As String
Dim lastrow As Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
.Pattern = "d+|\d+.\d+"
End With
Set MyRange = ActiveSheet.Range("a2:a" & 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, 1) = Outstring
Next

Set Collection = Nothing
Set RegExp = Nothing
Set MyRange = Nothing

End Sub

Mike

"Benjamin" wrote:

I'd like to see how to remove the alpha character from a cell, and
paste the contents to the cell next to it.
i.e. 430A, changes to 430, so I can strip just the number out of there.

Yesterday I was helped out with finding and alpha, how would I strip just
the numbers out of a text cell. As the numbers and letters are all consider a
text type in this case, in Excel.

Here's my code I used for my last instance. It worked great.

Sub Macro()
Dim Row_Count As Integer
Dim lastrow As Integer
lastrow = (ActiveWorkbook.Sheets(2).Cells(65536, 1).End(xlUp).Row) - 1
Row_Count = "2"

Do Until Row_Count = lastrow
If Right(Trim(Cells(Row_Count, 1)), 1) Like "[A-Za-z]" Then
Cells(Row_Count, 2).Value = "Alpha"
End If
Row_Count = Row_Count + 1
Loop

Sample Data that I'll be running my macro on:
430A
430A
430B
430B
V-57
V-58
V-59
V-60
V-61
V-62