How to replace every existing values of a column with formula?

Question

My sheet currently has already data. Unfortunately, later I realize that all values in column E should be validated and changed as necessary by calling my own user-defined function and passing the values as the function parameter. For example, just says I have the following data in column E:

E1: "This is a text in (E1)"
E2: "This is a text in (E2)"
...
E7000: "This is a text in (E7000)"

Now, I want that every values in column E to be changed by a formula like this:

E1: = RemoveBrackets("This is a text in (E1)")
E2: = RemoveBrackets("This is a text in (E2)")
...
E7000: = RemoveBrackets("This is a text in (E7000)")

Supposing Excel supports Regex, my problem can be solved easily. but unfortunately Excel doesn't. Could someone propose possible solutions to my problem?

Thanks in advance

Answer 1

1) Insert a new column "F"

2) Copy column E into F

3) Write in E1

 =RemoveBrackets(F1)

4) Drag the E1 value through E7000

5) Hide column F.

Edit 1

You can do it with several passes of the Find/Replace feature:

Select the Column E before each PASS.

PASS 1

Find:              <"This is a text in>
Replace with:      <RemoveBrackets("This is a text in>

PASS 2

Find:              <)">
Replace with:      <)")>

PASS 3

Find:              <RemoveBrackets(>
Replace with:      <=RemoveBrackets(>

Edit 2

VB Regex substitute string function can be used from Excel.
You may dowload a toolpack such as Morefunc which supports a REGEX.SUBSTITUTE udf, or you could do your own udf using THIS as guideline.

I am posting here the code from the second reference, just for link independence:

Public Function SearchNReplace1(Pattern1 As String, _
  Pattern2 As String, Replacestring As String, _
  TestString As String)
    Dim reg As New RegExp
    reg.IgnoreCase = True
    reg.MultiLine = False
    reg.Pattern = Pattern1
    If reg.Test(TestString) Then
        reg.Pattern = Pattern2
        SearchNReplace1 = reg.Replace(TestString, ReplaceString)
    Else
        SearchNReplace1 = TestString
   End If
End Function  

Please read the full article, as you should turn on the Microsoft VBScript Regular Expressions 5.5 option in Excel first.

HTH

Answer 2

Similar to belisarius' method, but doesn't leave a trace:

1. Insert column F
2. Insert into F1 the value =RemoveBrackets(F1)
3. Copy down through F7000.
4. Copy column F
5. Right click cell E1, Paste Special, choose "Values".
6. Delete column F