-4

I have thousands of cells in a column which all have a phone number formatted as

Chad Warren
  • 107
  • 2
  • 17
  • 1
    Possible duplicate of [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) – jontro Feb 23 '18 at 01:11
  • 1
    This is not a code/SQL/regex writing service, where you post a list of your requirements and language of choice and a code monkey churns out code for you. We're more than happy to help, but we expect you to make an effort to solve the problem yourself first. Once you've done so, you can explain the problem you're having, include the **relevant** portions of your work, and ask a specific question, and we'll try to help. – Ken White Feb 23 '18 at 01:12
  • 1
    Besides, why do you need a regex for this? It's two very simple find and replace operations. Select the entire column by clicking on it's header. Do a find/replace, replacing `(` with nothing and click *Replace all*. You'll see a message when it's done, and the find/replace dialog stays open. Do another find/replace, replacing `)` with `-` and click *Replace all*. When it's finished, close the message that's displayed and then close the find/replace dialog. Finished. Less time than it took me to write my two comments. – Ken White Feb 23 '18 at 01:17

1 Answers1

1

You do not need Regex for this. If your data is in column A and consists of constants (not formulas) then:

Sub NoRegex()
    Dim r As Range

    Set r = Columns(1).SpecialCells(2)

    r.Replace what:="(", replacement:=""
    r.Replace what:=")", replacement:="-"
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99