CODEWITHSUNDEEP
regexexcelvba
Aleksei Nikolaevich
Aleksei Nikolaevich

Reputation: 325

How to use regular expressions in Excel?

I have a column of values that can be numbers, letter, characters or both. I need to sieve that column into a new column with the following rules:

1) if a cell contains numbers then concatenate "89"

2) if a cell contains numbers and hyphens, trim the hyphens and concatenate 89

3) if a cell contains letters or other spec characters, say string

column    resultingColumn
1234      123489
12-34hk   string
&23412    string
99-9      99989
34-4      34489

I tried but its not as easy as it seemed

Function SC(strIn As String) As String
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
     With objRegex
     //i am not sure how to list the rules here
    .ignorecase = True
    SC = .Replace(strIn, vbNullString)
    End With
    End Function

Upvotes: 0

Views: 7184

Answers (2)

SheetJS
SheetJS

Reputation: 22905

You don't need a regular expression or even VBA for this: set B2 to the following formula and fill down:

 =IF(SUM(LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0,"-"},"")))-10*LEN(A2)=0,SUBSTITUTE(A2,"-","")&"89","string")

What this does is calculate the lengths if we remove each of the characters in the class [0-9\-] from the text. If there are no other characters, then we will have removed each of the characters once, so the total sum of lengths of the strings is 10 times the original string. If there are extraneous characters, they won't be deleted and so the sum will exceed the threshold.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

You do not require Regular Expressions. Consider:

Function aleksei(vIn As Variant) As Variant
    Dim z As Variant
    z = Replace(vIn, "-", "")
    If IsNumeric(z) Then
        aleksei = z & "89"
    Else
        aleksei = "string"
    End If
End Function

Edit#1:

Based on Nirk's comment, if the decimal point is to be excluded as part of a "number, then use instead:

Function aleksei(vIn As Variant) As Variant
    Dim z As Variant, zz As Variant
    z = Replace(vIn, "-", "")
    zz = Replace(z, ".", "A")
    If IsNumeric(zz) Then
        aleksei = z & "89"
    Else
        aleksei = "string"
    End If
End Function

Upvotes: 2

Related Questions