Have you downloaded a CSV file it’s full of leading apostrophes? Then you’ve come to the right place.
Removing apostrophes from a spreadsheet in Excel doesn’t have to be a painful process. Especially if there’s a lot of data!
Often, you don’t even realize the apostrophes are there until you try to manipulate the data. In a standard cell, they remain hidden and can only be seen when you see the cell contents in the formula bar.
In this article, I will show you how to quickly and seamlessly remove them with minimum risk of errors.
So, let’s get the show on the road!
Why Does The Leading Apostrophe Appear In Excel?
Before we dive into how to fix the issue, let’s just go through why they appear.
I’ve found that the most common reason is because of how Excel handles certain text data types.
For example, if a date is entered into a cell as a text type (e.g. 1-Jan-2023). Excel normally recognizes dates as a specific set of numbers, but sometimes the date has been inputted as text and is not picked up properly. Even if the cell contains a number or date, Excel will treat it as text.
Additionally, Excel can automatically add apostrophes when formatting cells as a currency, dates, or any other format that may use apostrophes as part of the formatting.
In some cases, apostrophes may also appear in Excel due to an issue with the underlying imported data. The source data itself is adding the apostrophe so you need to clean the data once it’s been imported into Excel.
Apostrophes can usually be traced back to the data being entered or imported into Excel, or the way that Excel is handling the data.
3 Methods To Remove Leading Apostrophes In Excel
These methods are in no particular order, but I usually like to start with the simplest – find and replace!
Find And Replace The Apostrophe
I admit this technique is a bit hit-and-miss and doesn’t always work, but it is very quick. This is really dependent on the data that you’re trying to manipulate. Here are the steps for using the “Find and Replace” function:
- Select the range of cells that contains the apostrophes you want to remove
- Press “Ctrl+H” to open the “Find & Replace” dialog box
- In the “Find what” field, type in an apostrophe (‘) and leave the “Replace with” field blank
- Click the “Replace All” button to remove all apostrophes from the selected range of cells
Alternatively, you can use the SUBSTITUTE function to remove apostrophes from a cell.
=SUBSTITUTE(A1,”‘”,””). Where A1 is the cell containing the apostrophes, this formula will replace all apostrophes in A1 and you can copy this formula down the column.
Using Text-to-Columns To Remove Apostrophes
Another way to remove leading apostrophes in Excel is by using the “Text to Columns” feature. Here are the steps to use the “Text to Columns” feature to remove leading apostrophes:
- Select the column or range of cells that contain the leading apostrophes
- Go to the “Data” tab in the ribbon
- Click on “Text to Columns“
- In the “Text to Columns” wizard, select “Delimited” and click on “Next”
- In the next step, uncheck all options in the “Delimiters” section and click on “Finish”
Multiplying The Cells With 1
Another way to remove leading apostrophes in Excel is by multiplying the cells with 1. What this does is it tries to force the cell to change the data type from a text type to a number or date type.
Here are the steps to use multiplication to remove leading apostrophes:
- In a blank cell anywhere on the spreadsheet, enter a 1 and copy that cell “Ctrl+C”
- Select the cell or range of cells you want to remove apostrophes
- Right-click and select “Paste Special”
- In the Paste Special menu, select “Values” (under Paste) and “Multiply” (under Operations)
- Click “OK”
Done! This will convert the text in those cells to numbers and remove the leading apostrophes.
Thanks for reading and I hope you found this helpful!
Other Excel tutorials you may also like:
Thanks!
It only works for me if I put the format from the CSV file.