Read more: VBA to Remove Characters from String in Excel ConclusionĮxcel datasets bear various cell formats, phone numbers are also one of them. If 0s are present at the beginning of the phone numbers, this method keeps them as it is. Execution of the steps produces a result similar to the image below Step 5: Select a range of cells you want to remove the dashes. Rng.NumberFormat = VBA.Replace(rng.Value, '-', '') Set WorkRng = Application.InputBox('Range', xTitleId, WorkRng.Address, Type:=8) Step 3: Paste the following code in the Microsoft Visual Basic Module. Step 2: In the Microsoft Visual Basic Toolbar, Click on Insert> Module. Step 1 : Press ALT+F11 altogether to open Microsoft Visual Basic. Step 2: Drag the Fill Handle up to the last entries and the execution depicts results similar to the image belowĪ VBA macro code removes dashes from a selected range of cells via a code run by Microsoft Visual Basic. Step 1: Enter formula =SUBSTITUTE(D4,”-”,””)in an adjacent cell. You can remove dashes & show phone numbers in another cell by simply with a formula using the SUBSTITUTE Function. Read more: How to Remove Special Characters in Excel Method 3: Using Formula Method Phone Numbers beginning with 0s also keep the 0s starting the number in this process. The result will similar to the image below Step 2: On the left of the Format Cell window inside Categories, Select Custom>Modify any format with eleven 0’s (as our phone number has 11 digits)

Step 1: Select the range of cells, you want to remove the dashes.Go to Home Tab > Click on Cell (section)>Click on Format>Select Format Cell. Read more: How to Remove Non-numeric Characters from Cells in Excel Method 2: Using Format Cell Make sure you copy the raw data before executing this method. Notes: Keep in mind that the Find & Select method changes the raw data. Click OK.Īll the Dashes/Hyphen gets automatically replaced as shown in the image below Step 4: A confirmation window will pop up. You can use pressing CTRL+H to bring out the Find & Replace window. Step 2: In the Replace Dialog Box, in Find What box type Dash/Hyphen (-) and Replace With box press the Null ( ). Step 1: Go to Home Tab> Click Find & Select (in Editing section)> Select Replace. 4 Easy Methods to Remove Dashes from Phone Numbers in Excel Method 1: Using Find & Select Method

Remove openoffice excel page number