16/03/2024
It's about to solve the Microsoft Excel related problem very easily.
How to separate numbers in column A from alphabet characters and output them in column B using VBA in Excel.
Code:
Sub ExtractNumbersFromString()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim regex As Object
Dim matches As Object
Dim match As Object
' Set the worksheet to the one you want to work with
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
' Create a regular expression object
Set regex = CreateObject("VBScript.RegExp")
regex.Global = True
regex.IgnoreCase = True
regex.Pattern = "\d+" ' Match one or more digits
' Find the last used row in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loop through each row in column A
For i = 1 To lastRow
' Check if the cell contains a string
If Not IsEmpty(ws.Cells(i, "A").Value) And IsStringNumeric(ws.Cells(i, "A").Value) Then
' Extract numerical values from the string using regular expression
Set matches = regex.Execute(ws.Cells(i, "A").Value)
' Concatenate all matched numbers into a string separated by space
For Each match In matches
ws.Cells(i, "B").Value = ws.Cells(i, "B").Value & " " & match.Value
Next match
' Trim leading space
ws.Cells(i, "B").Value = Trim(ws.Cells(i, "B").Value)
Else
' If it's not a string or doesn't contain any numbers, leave the corresponding cell in column B empty
ws.Cells(i, "B").Value = ""
End If
Next i
End Sub
Function IsStringNumeric(str As String) As Boolean
' Check if a string contains at least one numerical character
Dim i As Integer
For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
IsStringNumeric = True
Exit Function
End If
Next i
IsStringNumeric = False
End Function
