CODEWITHSUNDEEP
regexvbaexcelexcel-2010
Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

Separating strings from numbers with Excel VBA

I need to

a) separate strings from numbers for a selection of cells

and

b) place the separated strings and numbers into different columns.

For example , Excel sheet is as follows:

     A1          B1
  100CASH     etc.etc.

The result should be:

   A1            B1          C1
  100           CASH       etc.etc.

Utilization of regular expressions will be useful, as there may be different cell formats,such as 100-CASH, 100/CASH, 100%CASH. Once the procedure is set up it won't be hard to use regular expressions for different variations.

I came across a UDF for extracting numbers from a cell. This can easily be modified to extract string or other types of data from cells simply changing the regular expression.

But what I need is not just a UDF but a sub procedure to split cells using regular expressions and place the separated data into separate columns.

I've also found a similar question in SU, however it isn't VBA.

Upvotes: 1

Views: 3121

Answers (2)

PaulStock
PaulStock

Reputation: 11263

See if this will work for you:

UPDATED 11/30:

Sub test()

    Dim RegEx As Object
    Dim strTest As String
    Dim ThisCell As Range
    Dim Matches As Object
    Dim strNumber As String
    Dim strText As String
    Dim i As Integer 
    Dim CurrCol As Integer


    Set RegEx = CreateObject("VBScript.RegExp")
    ' may need to be tweaked
    RegEx.Pattern = "-?\d+"

    ' Get the current column
    CurrCol = ActiveCell.Column

    Dim lngLastRow As Long
    lngLastRow = Cells(1, CurrCol).End(xlDown).Row

    ' add a new column & shift column 2 to the right
    Columns(CurrCol + 1).Insert Shift:=xlToRight

    For i = 1 To lngLastRow  ' change to number of rows to search
        Set ThisCell = ActiveSheet.Cells(i, CurrCol)
        strTest = ThisCell.Value
        If RegEx.test(strTest) Then
            Set Matches = RegEx.Execute(strTest)
            strNumber = CStr(Matches(0))
            strText = Mid(strTest, Len(strNumber) + 1)
            ' replace original cell with number only portion
            ThisCell.Value = strNumber
            ' replace cell to the right with string portion
            ThisCell.Offset(0, 1).Value = strText
        End If
    Next

    Set RegEx = Nothing
End Sub

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

How about:

Sub UpdateCells()
Dim rng As Range
Dim c As Range
Dim l As Long
Dim s As String, a As String, b As String

''Working with sheet1 and column C
With Sheet1
    l = .Range("C" & .Rows.Count).End(xlUp).Row
    Set rng = .Range("C1:C" & l)
End With

''Working with selected range from above
For Each c In rng.Cells
    If c <> vbNullString Then
        s = FirstNonNumeric(c.Value)

        ''Split the string into numeric and non-numeric, based
        ''on the position of first non-numeric, obtained above. 
        a = Mid(c.Value, 1, InStr(c.Value, s) - 1)
        b = Mid(c.Value, InStr(c.Value, s))

        ''Put the two values on the sheet in positions one and two 
        ''columns further along than the test column. The offset 
        ''can be any suitable value.
        c.Offset(0, 1) = a
        c.Offset(0, 2) = b
    End If
Next
End Sub

Function FirstNonNumeric(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "[^0-9]"
        FirstNonNumeric = .Execute(txt)(0)
    End With
End Function

Upvotes: 0

Related Questions