Make Excel formula treat string as regex

Question

In my Excel workbook, there are a lot of occurrences of 00XX, where X can be any digit. For instance, 0017 appears frequently. I want to remove all occurrences of 00XX using the SUBSTITUTE function. So far, I have this:

=SUBSTITUTE(C1,"00[0-9][0-9]","")

This is not working though, and I think it is because Excel treats the second string as a literal, instead of a regular expression. Is there a way to make this formula work? I know that some programming languages allow you to add modifiers to a string to change its meaning, but I have not found any such thing yet for Excel.

use UDF with regular expressions: http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Dmitry Pavliv, May 28 '14 at 15:58

score 0 · Answer 1 · answered May 28 '14 at 16:08

This solution uses no VBA

Pick an unused column, say column K.

In K1 enter:

="00" & TEXT(ROW()-1,"00")

copy this down from K2 thru K100. This is a list of all possible "undesirable" sub-strings.

With raw data in A1, in B1 enter the array formula:

=IFERROR(TRIM(SUBSTITUTE(A1,INDEX(K1:K100,MATCH(1,COUNTIF(A1,"*"&K1:K100&"*"),0)),"")),A1)

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

If A1 contains:

t0078yetryet

B1 would display:

tyetryet

This sounds like a quite feasible solution. I'll definitely give it a try when I'm at work tomorrow. — Lee White, May 29 '14 at 11:49

score 0 · Answer 2 · edited May 30 '14 at 05:33

0

Why not simply take advantage of the fact that the SEARCH function accepts wildcards?

=REPLACE(A1,LOOKUP(2^15,FIND(TEXT(ROW(INDEX(A:A,1):INDEX(A:A,99)),"0000"),A1)),4‌,"")

Note that neither of the two solutions so far proposed will replace more than one occurrence of such substrings simultaneously on a given string.

Regards

edited May 30 '14 at 05:33

brettdj

54,857
16
114
177

answered May 28 '14 at 16:52

XOR LX

7,632
1
16
15

=REPLACE(A1,LOOKUP(2^15,FIND(TEXT(ROW(INDEX(A:A,1):INDEX(A:A,99)),"0000"),A1)),4,"") – XOR LX May 29 '14 at 12:37

score 0 · Answer 3 · answered May 29 '14 at 11:15

Use the MoreFunc addin (web link seems to be downe
VBA UDF below, use such as =CleanString(A1)

test

Sub TestDaCode()
MsgBox CleanString("0018 2450 00notme 0017")    
End Sub

udf

Function CleanString(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Global = True
    .Pattern = "00\d{2}"
    CleanString = .Replace(strIn, vbNullString)
End With
End Function

Make Excel formula treat string as regex

3 Answers3