When you’re juggling data in Microsoft Excel, it’s not unusual to come across a dataset sprinkled with dashes that need to be removed. Whether it’s cleaning up Social Security Numbers or just prepping data for a uniform look, getting rid of those tiny lines can be essential. Luckily, Excel offers several straightforward methods that can help tidy up your workbook quickly.

I’ll guide you through four simple techniques to strip away those unwanted dashes from your Excel data. No matter your Excel expertise, these methods will help you achieve a cleaner dataset. Plus, each technique serves a different need, so you’ll have multiple tools in your belt for various Excel scenarios.

Key Takeaways

  • Flash Fill allows for quick dash removal in consistent datasets.
  • The Find and Replace feature can search and eliminate dashes across the workbook.
  • Formulas offer a dynamic approach to remove dashes when working with data in Excel.

Method 1 – Remove Dashes Using Flash Fill

Using Flash Fill in Excel is an absolute breeze when it comes to tidying up phone numbers or social security numbers that have hyphens cluttering the view. For those unfamiliar, it’s quite the handy technique for when you’ve got a cell range filled with similar patterns—like dashes in consistent places—that you need to get rid of.

Flash Fill gets savvy with patterns, spotting the secret rhythm in the data. To wield this method, I simply type what I expect to see without the dashes into a cell next to the cluttered data. Imagine I’ve got a column of Social Security numbers awkwardly separated by dashes. Here’s my approach:

  • I type the clean version of the first number, free of any dashes, into the adjacent cell (B2). This tells Excel, “Hey, this is what I’m expecting!”

    Enter the expected result in Cell B2

  • Next up, I smack the Enter key, which moves me down to cell B3.

  • Then, it’s showtime for the Flash Fill magic. I could take the scenic route by clicking through the Home tab, hunting down the Fill icon, and choosing Flash Fill. But who has time for that? Instead, I press and hold the Control key and tap E for efficiency.

    click on the flash fill icon

Presto! Excel whips up the results, filling the column with dash-free numbers.

result after flash fill where dashes have been removed

That’s not all, folks. Suppose I wanted to keep the party halfway and only wipe out the first hyphen? Simple. I input ‘695-77990’ in B2, suggesting, “Keep the last dash, please!” and Flash Fill follows my lead.

Just a heads up though, computers can get cheeky sometimes. While Flash Fill is often on the ball, double-check to make sure it hasn’t goofed off. Got an odd or inconsistent result? Try filling out the expected digits in two cells before letting Flash Fill take the reins again.

So, that’s the lowdown on dash removal with Flash Fill—a cool party trick for your Excel toolkit.

Method 2 – Remove Dashes Using Find and Replace

When I’m dealing with data in Excel that has unnecessary dashes, especially in columns where they’re just clutter, I find that using Find and Replace is a swift way to clean things up. Let’s dive right into how I do it:

  • First things first, I select the cells in the column that are littered with those pesky dashes.
  • Then I navigate to the Home tab, which houses the Editing group.
  • From there, I go for the Find and Select tool, specifically zeroing in on the Replace feature to bring up the Find and Replace dialog box.
editing group on the Home tab

Tough to remember? No worries. The keyboard shortcut Ctrl + F gets me there in a jiffy.

  • In the dialog box that pops up, the Find what field is where I type in the dash character “-” – since that’s what I’m aiming to get rid of.
find what field
  • The Replace with field? I leave that baby blank. That’s right, nothing goes in there because I’m looking to replace the dash with, well, nothing.
leave replace with field blank
  • Once everything’s set, I hit the Replace All button. It’s like telling all the dashes to scram at once.
click Replace All
  • Excel’s going to throw me a message, telling me the number of replacements it made. I click OK, and it’s mission accomplished – the dashes are wiped out, leaving me with clean data.

Remember though, when I use this method, it’s an all-or-nothing deal. It’s great when I want to remove all dashes within my selected cells, but it won’t let me be choosy about which dashes to remove. If I want more control, like taking out only the first or last dash, this isn’t the route I’d take.

Method 3 – Remove Dashes Using Formula

When you’re managing data in Excel and you bump into numbers and social security numbers (SSN) all tangled up with dashes, the SUBSTITUTE function comes to the rescue. It’s pretty nifty for tidying up the data without breaking a sweat, especially when dealing with phone numbers.

Here’s the scoop on how I wield the SUBSTITUTE function to zap those pesky dashes:

  1. Whip out the formula: =SUBSTITUTE(A2,"-","")
  2. Punch this into cell B2 right next to your dash-riddled numbers or SSN in column A.
  3. Drag the formula down to other cells to apply the magic across the board.

This is what’s happening under the hood of that formula:

  • The first argument is the cell reference where Excel spots the dashes – that’s A2 in our case.
  • The second argument is what you’re looking to replace; put a dash in double quotes like this: "-".
  • The third argument is what you want to swap in for the dash. I’m aiming to make the dashes vanish, so I pop in an empty string: "".

For the curious cats out there, the SUBSTITUTE formula can also pinpoint specific dashes to disappear. By adding a fourth argument, you can command Excel to target the first, second, or whichever dash you want gone. Skip the fourth argument and you’ll wipe out all dashes in one go.

Here’s a visual aid to help you see what I mean:

And that’s the lowdown on using the SUBSTITUTE function to neaten up your Excel sheets by dumping those unnecessary dashes. Simple, right?

Method 4 – Remove Dashes Using Power Query

When you’re knee-deep in data preparation, there’s a nifty feature in Excel that can help you shed unwanted characters like dashes from your data—especially useful when dealing with SSN data or other consistent datasets. That’s where Power Query flexes its muscles for those who already have it in their Excel toolkit.

Here’s my step-by-step guide to using Power Query for ditching those pesky dashes:

  • First things first, I kick things off by turning the dataset into an Excel table. This is super simple: just select your data and head to the Insert tab, then click on Table.

    click on the table icon in the insert group

  • In the Create Table dialog, I make sure the range is correct and hit OK.

  • Next, the Data tab is where the action continues. I use the From Table/Range option to bring the Power Query editor to the party.

    click the data tab and then click on from table range

  • Right-clicking the column header in Power Query and picking Replace Values gets me where I need to be.

    right click on the column header and then click on replace values

  • In the ‘Value to Find’ bit, a dash goes in, and I leave the ‘Replace with’ field as empty as a ghost town.

    leave the replace with field blank

  • Smashing the OK button, I watch as dashes vanish like magic across my dataset.

  • To wrap it up, I click Close and Load, and behold—a new worksheet with a dash-free table emerges.

    power query result inserted in a new sheet

What’s cool about this method is it’s not a one-trick pony. Once I’ve set up the flow in Power Query, every time fresh data rolls in with those unwelcome dashes, I can simply refresh the query. No need to mess around with VBA codes or repeat steps. And who doesn’t love a bit of smart automation that makes data visualization cleaner and more accurate without extra fuss?

By creating this flow once, updating the source data and refreshing the query is all it takes to keep my datasets dash-free and clean.

Similar Posts