When working with Excel on my Mac, I often find myself in a tangle with data formatting. Whether it’s social security numbers, dates, or phone numbers, those pesky dashes always seem to appear where they’re least wanted. And let’s not even start on the errors they can cause when sorting or analyzing data. I’ve discovered that Excel has several neat tricks up its sleeve to help eliminate these unwanted dashes, ensuring that the data looks clean and is ready for use.

I must say, beyond just numbers and dates, formatting in Excel can be a real doozy when you’re trying to maintain consistency across your cells. I’ve learned that special formatting needs, such as tweaking cell borders or removing dashes, require a delicate touch and a bit of know-how. Luckily, with a few formulas and some clever use of the tools available, whipping those cells into shape becomes a less daunting task.

Remove Dashes from an SSN (Social Security Number)

When working with SSN data in Excel, you may find yourself needing to strip away formatting, such as dashes. This is a breeze with the SUBSTITUTE function. Here’s a step-by-step breakdown:

  1. Click on the cell where you’d like to display the cleaned SSN.
  2. Type in the formula =SUBSTITUTE( to begin.
  3. Click on the cell that contains the SSN you’re adjusting.
  4. After a comma, add a dash in quotes like so: "-".
  5. Then, specify an empty string as the replacement: ,"".
  6. Close off your formula with a parenthesis and hit enter.

Your cell will now show an SSN that’s dash-free. Just in case you’re starting fresh and inputting SSNs, remember you can format cells to display SSNs with dashes without actually altering the value. Just follow these breadcrumbs: Format Cell Options → Numbers → Special → Social Security Number.

Use Find and Replace to Remove Dashes in Excel

When I’ve got data with hyphens cluttering it up, here’s what I do:

  • First, I select the cells that have those pesky dashes.
  • Then, I hit Ctrl + H to bring up the Find and Replace dialogue.
  • In the Find what box, I type a dash (hyphen).
  • I leave the Replace with box empty since I’m removing the dashes.
  • Finally, I click Replace All.

All dashes in my selected range vanish instantly. If I forget to select cells, the entire sheet gets cleaned up. And if I need to sweep through the whole workbook, I can just use the Options inside Find and Replace to extend my dash-busting crusade.

It’s practically magic – but in Excel.

Use VBA Codes to Remove all the Dashes

So, I’ve got this neat VBA script that’ll zap those pesky dashes straight out of your Excel data. Here’s the lowdown:

Sub remove_dashes()
    Dim iCell As Range
    For Each iCell In Selection.Cells
      iCell.Value = Replace(iCell, "-", "")
    Next iCell
End Sub

I just select the cells I want to clean, fire up this script, and poof – no more dashes. In case my data’s got phone numbers or social security numbers, and I want to keep everything tidy with a specific format, this tweaked version here is my go-to:

Sub remove_dashes_SSN_format()
    Dim iCell As Range
    For Each iCell In Selection.Cells
      iCell.Value = Replace(iCell, "-", "")
      iCell.NumberFormat = "000-00-0000"
    Next iCell
End Sub

This not only strips out the dashes but also formats the numbers like they’re SSNs. It’s perfect when I’m dealing with a consistent dataset and need things formatted just right.

Oh, and to actually run either of these snippets, I just use the Control Key + F11 to open my VBA editor. Simple, right?

For a more visual crowd, there’s a helpful image that shows precisely what the output looks like after using the VBA code. Check it out here.

Want to dig deeper into more advanced Excel tricks? I’d totally recommend swinging by these Advanced Excel Tutorials. They’ve got a treasure trove of tips that can help automate all sorts of stuff in your workbooks.

Related Tutorials

TutorialDescription
Multiply in Excel using Paste SpecialLearned to speed up my calculations.
Add Barcode in ExcelAdding barcodes made my inventory tracking a breeze.
Change Default Font in ExcelPersonalizing my default settings was a neat trick.
Filter by Color in ExcelColor-coding data and then filtering it? Yes, please!
Rename a Table in ExcelOrganizing my data just got clearer.

Similar Posts