...

MS Excel VBA

How To Separate Text And Numbers In Excel using VBA macros

How To Separate Text And Numbers In Excel using VBA macros

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  
The Nothing keyword is utilized to disassociate an object or variable from an actual thing.

Leave a Comment

Your email address will not be published. Required fields are marked *


Scroll to Top