1

I am trying to use the following expression to locate a pattern of text in my Excel data. The goal is to then remove the text once it is located.

/.([0-9]+[]?x[]?[0-9]+[]?dpi)./i

Help!

M.C.
  • 27
  • 1
  • 1
  • 3
  • 2
    Welcome to Stack Overflow. It is recommended that you show what you have tried so far -- specific problems tend to get the most attention. Could you provide an example? – JimmyPena Mar 19 '12 at 19:27

2 Answers2

6

I have made a User Defined Function to run a regex search and display the final match in the cell.

=udfRegEx([Cell you want to find the expression],[Cell with the regular expression you want to use])

You need to open the Visual Basic editor and put the following code into a Module:

Function udfRegEx(CellLocation As Range, RegPattern As String)

Dim RegEx As Object, RegMatchCollection As Object, RegMatch As Object
Dim OutPutStr As String

    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True
        .Pattern = RegPattern
    End With

        OutPutStr = ""
        Set RegMatchCollection = RegEx.Execute(CellLocation.Value)
        For Each RegMatch In RegMatchCollection
            OutPutStr = OutPutStr & RegMatch
        Next
        udfRegEx = OutPutStr

    Set RegMatchCollection = Nothing
    Set RegEx = Nothing
    Set Myrange = Nothing

End Function

Also don't forget to add the Reference for Microsoft VBScript Regular Expressions 5.5

Emeralds
  • 61
  • 1
  • 4
  • Great function but is there a reason for your definition of 'i'? It makes the function not work for me in excel 2013 and I don't see its value used anywhere. Commenting it out seems to fix the problem though. – user2027202827 Sep 05 '13 at 01:06
  • I had it defined for a use in the function, but it looks like my function was edited. No need for 'i' anymore – Emeralds Sep 23 '13 at 19:50
2

You didn't specify it but I assumed it was using a VBA macro. i don't think you can do regular expression directly in the sheet using formula.

The following link should help you with regular expression and VBA:

http://www.regular-expressions.info/vb.html

Just be sure to add the correct reference "Microsoft VBScript Regular Expressions 5.5"

Hope this help

Regis
  • 375
  • 4
  • 16