How To Separate Text And Numbers In Excel using vba macros
Regular Expressions (Regno or Regtext) are frequently used in the developer world. They are used to validate website password information, search for text expression patterns, and many other uses. When using Regno in VBA, patterns are based. In our example file, column (B) cells contain mixed (numbers, letters, etc.) data. We want to catch the numeric values from these data and list them in column C and the non-numeric values in column D.
'For More : https://msexcelvba.com
Sub Seperation_pro()
Dim Regno, Regtext As Object, RegMatchColl As Object, RegMatch As Object
Dim rng As Range, rng2 As Range, STR As String
Set Regno = CreateObject("vbscript.regexp")
With Regno ' This pattern is to separate Numbers
.Global = True
.Pattern = "\d+"
End With
Set rng = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each rng2 In rng
STR = ""
Set RegMatchColl = Regno.Execute(rng2.Value)
For Each RegMatch In RegMatchColl
STR = STR & RegMatch
Next
rng2.Offset(0, 1) = STR
Next
Set Regtext = CreateObject("vbscript.regexp")
With Regtext 'This pattern is to separate Non-Numeric Characters (letter etc.)
.Global = True
.Pattern = "[^0-9]"
End With
For Each rng2 In rng
STR = ""
Set RegMatchColl = Regtext.Execute(rng2.Value)
For Each RegMatch In RegMatchColl
STR = STR & RegMatch
Next
rng2.Offset(0, 2) = STR
Next
Set RegMatchColl = Nothing
Set Regno = Nothing
Set Regtext = Nothing
Set rng = Nothing
End Sub
Code Explanation:
Dim Regno, Regtext As Object, RegMatchColl As Object, RegMatch As Object
Dim rng As Range, rng2 As Range, STR As String
Declare the Multiple variables.
Set Regno = CreateObject("vbscript.regexp")
With Regno ' This pattern is to separate Numbers
.Global = True
.Pattern = "\d+"
End With
Regno pattern to capture numeric values (numbers) : excel regular expression
Set rng = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each rng2 In rng
STR = ""
Set RegMatchColl = Regno.Execute(rng2.Value)
For Each RegMatch In RegMatchColl
STR = STR & RegMatch
Next
rng2.Offset(0, 1) = STR
Next
set range and for loop
Set Regtext = CreateObject("vbscript.regexp")
With Regtext 'This pattern is to separate Non-Numeric Characters (letter etc.)
.Global = True
.Pattern = "[^0-9]"
End With
This pattern is to separate Non-Numeric Characters (letter etc.)
For Each rng2 In rng
STR = ""
Set RegMatchColl = Regtext.Execute(rng2.Value)
For Each RegMatch In RegMatchColl
STR = STR & RegMatch
Next
rng2.Offset(0, 2) = STR
Next
set range and for loop
Set RegMatchColl = Nothing
Set Regno = Nothing
Set Regtext = Nothing
Set rng = Nothing
