Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
Thank you much Mike. I especially liked learning about a new object...
("vbscript.RegExp) That worked like a charm! Excellent. Much oblidged. "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
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" wrote: 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" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
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" wrote: 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" wrote: 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" 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Alpha from AlphaNumeric String...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove Numbers from Alphanumeric String | Excel Discussion (Misc queries) | |||
HOW DO YOU COUNT ALPHA CHARACTERS IN AN ALPHANUMERIC FIELD | Excel Discussion (Misc queries) | |||
How do I replace last numeric string from a alphanumeric string? | Excel Discussion (Misc queries) | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
Strip Alpha Characters out of an Alphanumeric Dataset | Excel Programming |