Do you want to learn how to remove apostrophes in Microsoft Excel? Follow along with this effortless tutorial.

When you attempt to remove apostrophes in Excel you feel like fixing a messy database. You find it surprising that Excel doesn’t consider numbers as numbers and you can’t apply formulas or sort data. That’s because hidden apostrophes are blocking Excel from treating them as real numbers.

Removing these apostrophes isn’t as straightforward as it seems, but don’t worry—I’ll guide you through simple, effective methods to fix this. By the end of this tutorial, you’ll clean up your data effortlessly. Let’s jump in and solve this Excel headache once and for all!

Using Flash Fill

You can use the Flash Fill tool, available in any of the latest Excel desktop apps since Excel 2016, to remove apostrophes from your dataset. This is an automated process. The Flash Fill tool reads patterns from your tasks and tries to mimic those. So, all you need to do is show Excel how and from where to remove the apostrophes. If there’s a pattern, Excel shall detect it and apply it to the rest of the column. Let me show you how with a real-world example.

Open the source dataset. Select a cell adjacent to the column where numbers with leading apostrophes are available.

There, manually type the number, except the apostrophe. Repeat this for a few more cells down the column.

Now, select the entire column as shown in the screenshot above.

Press Ctrl + E on the keyboard to use Flash Fill. Alternatively, click on the Flash Fill command button in the Data Tools block of the Data tab.

Excel should follow your pattern and remove the apostrophes instantly.

The drawback of this method is the Flash Fill tool isn’t available in dated Excel apps before Excel 2016.

Using the Paste Special Tool

This method is only applicable to numbers in an Excel worksheet that shows the Preceded by an apostrophe error alert in one or more cells.

Type 0 in an empty cell in the active worksheet.

Select the cell and press Ctrl + C.

Highlight the source data column and press Ctrl + Alt + V to bring up the Paste Special dialog box.

There, select Values in the Paste column and Add in the Operation column.

Click OK to apply the Paste Special configuration.

You’ll see that Excel has changed the text-like numbers to actual numbers ready for further use in formulas.

Using Find and Replace

For compatibility reasons, Find and Replace is the best tool to remove unwanted apostrophes in any Excel dataset. This tool is available from Excel 2.0. Let me walk you through how to use the tool.

Firstly, go to the target dataset and press Ctrl + H to launch the Find and Replace tool.

Type an apostrophe (') symbol in the Find what field.

Leave the Replace with field empty.

Hit the Replace All button on the Find and Replace dialog box.

Excel will remove the apostrophes and show a confirmation dialog box showing the exact number of apostrophes removed.

This process is only effective if the apostrophes are visible in the cells of the worksheet.

Certain cells of the dataset might contain hidden apostrophes that are only visible when you select the cell and look at the formula bar.

For such cells, use the Text to Columns tool to remove hidden apostrophes. I’ve outlined the steps later in this Excel tutorial.

Using Text to Columns

Another quick and efficient way to remove apostrophes in Excel is the Text to Columns tool. Let me show you how to use it in simple steps with a real-world dataset.

Let’s consider, that you have an Excel worksheet containing prices of certain Apple devices. Due to the database structure of the external or internal software that you used to gather this data, one or more apostrophes were added to the raw data.

Highlight the entire column that needs fixing by selecting the first cell and pressing Ctrl + Shift + Down Arrow.

Excel will quickly select the entire column up to which data is available.

Click on the Text to Columns command in the Data Tools block of the Data tab.

The Convert Text to Columns Wizard will show up. Click Next.

Checkmark the Other box in the next screen of Text to Columns and enter the apostrophe (') symbol. This should be in the Delimiters column.

Check the Data Preview section just below the Delimiters column. Here, you’ll see what the output dataset will look like. Click Next.

Simply hit the Finish button on the next screen.

Excel will shift numbers with leading apostrophes to the adjacent column on the right side.

Values with trailing apostrophes won’t move from the original location.

To merge these values in one column, select the first cell in the adjacent column, like D in the current tutorial. Enter the following formula in D2:

=IF(B2<>"", B2, C2)    

Make sure you change the cell range references in the above formula according to your dataset.

Hit Enter to calculate the cell.

Use the fill handle of the first cell and drag it down the column to replicate the formula to the rest of the cells.

That’s it! You have successfully merged the separated values that were generated when using the Text to Columns tool.

You can now hide the redundant columns to declutter the worksheet.

Using TRIM and CLEAN Functions

If you need to use an Excel formula to remove apostrophes, you can use a combination of the TRIM and CLEAN functions. It doesn’t simply act on the apostrophes but any unwanted spaces and non-printable characters. This method is specifically useful if you are removing apostrophes from text strings. Let me show you how below.

Select the adjacent cell of the source text string and enter the following formula into the cell:

=TRIM(CLEAN(A2))

Don’t forget to change A2 to an appropriate cell address that matches your own dataset.

Hit Enter to calculate the cell.

You can drag the fill handle down the column if you need to cover more cells in the same column.

The formula will remove all unwanted apostrophes.

The data you see now is in formula format. If you need actual text strings, copy the formula cells in the new column where you’ve applied the TRIM and CLEAN combination formula.

Press Ctrl + C to copy the content of the selected cell range.

Now, press Ctrl + Alt + V to bring up the Paste Special dialog box.

Select Values in the Paste column and click OK.

You’ve successfully converted the formula cells into actual text strings.

Using Power Query

If you’re handling a large dataset that might slow down Excel or won’t fit completely in one worksheet, you often use Power Query. It allows you to transform and clean raw data extracted from external servers or CRM software. The resulting data becomes compact so you can load it easily in an Excel sheet.

There are two ways to import data to the Power Query Editor. You can either do that using any of the multiple data connectors in the Get Data drop-down menu or from the active worksheet.

So, if you’re getting data from an external server, click on the Get Data command in the Data tab of Excel.

Hover the cursor over your preferred primary data source, like From Azure, From Online Services, etc., to open an overflow menu on the right.

There, you can choose more data connectors for external servers, like From Azure SQL Database, From SharePoint Online List, etc.

Select an appropriate option and follow onscreen instructions to import your dataset.

Alternatively, if your dataset is in the active worksheet, select the entire dataset and click on the From Table/Range command.

Click OK on the Create Table dialog box.

Your data will show up in the Power Query Editor interface.

Now, select the column that contains numbers or text strings with apostrophes.

Go to the Transform tab and click on the Replace Values drop-down list. Click on Replace Values.

You’ll see the Replace Values dialog box. There, enter an apostrophe (') symbol in the Value to Find field and keep the Replace With field empty.

Click OK to apply the data transformation you’ve set up.

Power Query will remove the apostrophes from the selected column.

Click on the File tab and select the Close & Load To option.

You’ll see the Import Data dialog box. Click on the Existing worksheet option and select a cell as the destination for the data you’ll import.

Click OK to apply.

Excel will import the transformed data from Power Query.

You can now delete the previous columns to declutter the worksheet.

From now on, you can only update the source data either in the worksheet or external dataset level. Then, simply close and reopen Excel or reload by pressing Ctrl + Alt + F5.

So, if you need to remove apostrophes routinely from external or internal datasets, this is the best way to follow.

Now you should know how to remove apostrophes in Microsoft Excel using various methods, including Excel UI commands, functions, and Power Query.

Try the method you like and leave a comment below to share your experience.