Remove Alpha from AlphaNumeric String...

B

Benjamin

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
 
J

Jacob Skaria

Try the below...The worksheet is not referenced correctly

Sub Macro()
Dim ws As Worksheet
Dim Row_Count As Long, lastrow As Long
Set ws = ActiveWorkbook.Sheets(2)

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

End Sub

If this post helps click Yes
 
M

Mike H

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
 
B

Benjamin

Thank you much Mike. I especially liked learning about a new object...
("vbscript.RegExp) That worked like a charm! Excellent.
Much oblidged.

Mike H said:
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 said:
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
Click to expand...
Click to expand...
 
B

Benjamin

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jacob Skaria

I misread your query....

Sub Macro()
Dim ws As Worksheet
Dim Row_Count As Long, lastrow As Long
Set ws = ActiveWorkbook.Sheets(2)

lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row - 1
Row_Count = 2
Do Until Row_Count = lastrow
For intTemp = 1 To Len(Range("A" & Row_Count))
If Not IsNumeric(Mid(Range("A" & Row_Count), intTemp, 1)) Then
strData = strData & Mid(Range("A" & Row_Count), intTemp, 1)
End If
Next
Range("B" & Row_Count) = strData: strData = ""
Row_Count = Row_Count + 1
Loop

End Sub

If this post helps click Yes
---------------
Jacob Skaria


Jacob Skaria said:
Try the below...The worksheet is not referenced correctly

Sub Macro()
Dim ws As Worksheet
Dim Row_Count As Long, lastrow As Long
Set ws = ActiveWorkbook.Sheets(2)

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

End Sub

If this post helps click Yes
---------------
Jacob Skaria


Benjamin said:
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
Click to expand...
Click to expand...
 
J

Jacob Skaria

Extra * Not * has just reversed the answer...Try the below

Sub Macro()
Dim ws As Worksheet
Dim Row_Count As Long, lastrow As Long
Set ws = ActiveWorkbook.Sheets(2)

lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row - 1
Row_Count = 2
Do Until Row_Count = lastrow
For intTemp = 1 To Len(Range("A" & Row_Count))
If IsNumeric(Mid(Range("A" & Row_Count), intTemp, 1)) Then
strData = strData & Mid(Range("A" & Row_Count), intTemp, 1)
End If
Next
Range("B" & Row_Count) = strData: strData = ""
Row_Count = Row_Count + 1
Loop

End Sub

If this post helps click Yes
---------------
Jacob Skaria


Jacob Skaria said:
I misread your query....

Sub Macro()
Dim ws As Worksheet
Dim Row_Count As Long, lastrow As Long
Set ws = ActiveWorkbook.Sheets(2)

lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row - 1
Row_Count = 2
Do Until Row_Count = lastrow
For intTemp = 1 To Len(Range("A" & Row_Count))
If Not IsNumeric(Mid(Range("A" & Row_Count), intTemp, 1)) Then
strData = strData & Mid(Range("A" & Row_Count), intTemp, 1)
End If
Next
Range("B" & Row_Count) = strData: strData = ""
Row_Count = Row_Count + 1
Loop

End Sub

If this post helps click Yes
---------------
Jacob Skaria


Jacob Skaria said:
Try the below...The worksheet is not referenced correctly

Sub Macro()
Dim ws As Worksheet
Dim Row_Count As Long, lastrow As Long
Set ws = ActiveWorkbook.Sheets(2)

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

End Sub

If this post helps click Yes
---------------
Jacob Skaria


Benjamin said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

Mike H

Use this pattern

..Pattern = "\d"

Mike
Benjamin said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

Assuming your values are as indicated by your examples (either the value
starts with the number you want or it ends with a dash followed by the
number you want), then you can use this simpler macro construction...

Sub ExtractNumbers()
Dim C As Range, Source As Range, LastRow As Long
Set Source = Range("A2:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)
For Each C In Source
If IsNumeric(Left(C.Value, 1)) Then
C.Offset(0, 2).Value = Val(C.Value)
Else
C.Offset(0, 2).Value = Mid(C.Value, InStrRev(C.Value, "-"))
End If
Next
End Sub

--
Rick (MVP - Excel)


Benjamin said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

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 said:
Use this pattern

.Pattern = "\d"

Mike
Benjamin said:
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 said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

colour format text when copied from text box 4
Macro for Multiple Worksheets 6
Stop duplicate record 3
Statement to Automate new sheets if is needed it. 3
I need to change from "=E7" to (equals the current value of E7) 3
NumberFormat = "@" syntax 1
Remove from listbox 4
Vaiable Help in VBA 5

Top