Remove leading apostrophes from numbers in Excel – 4 methods
In Excel, numbers formatted as text are often preceded by a leading apostrophe, which can interfere with data processing and calculations. This common issue arises from importing data from other sources or from misformatted user input. Removing these leading apostrophes is essential for accurate data analysis and manipulation. This guide explores four methods: using the VALUE function with Paste Values, Kutools for Excel, Text to Columns, and VBA code.
- Remove leading apostrophes with VALUE and Paste Values
- Remove leading apostrophes with Kutools for Excel
- Remove leading apostrophes with Text to Columns
- Remove leading apostrophes with VBA
- Frequently asked questions
Comparison of methods for removing leading apostrophes
| Method | Changes original cells directly | Best for | Key consideration |
|---|---|---|---|
| VALUE and Paste Values | After pasting the results | Keeping the original data until the results are verified | Requires a temporary helper column. |
| Kutools for Excel | Yes | Quickly processing a selected range | Requires Kutools for Excel. |
| Text to Columns | Yes | A continuous column of text-formatted numbers | Processes one column at a time. |
| VBA | Yes | Repeatable or larger conversions | Requires a macro-enabled workbook. |
Remove leading apostrophes with VALUE and Paste Values
The VALUE function converts text-formatted numbers into true numbers. You can then paste the results as values to replace the original data.
- Suppose the numbers with leading apostrophes begin in cell A2. In a blank cell beside the data, enter the following formula:
=VALUE(A2) - Press Enter, and drag the fill handle down to convert the remaining numbers.
- Select the formula results and press Ctrl + C. Right-click the first cell of the original range, and select Values under Paste Options.
- The leading apostrophes have been removed, and the entries are now numeric values.
Remove leading apostrophes with Kutools for Excel
Kutools for Excel's Convert between Text and Number feature simplifies the process, allowing you to clean multiple cells quickly without repetitive manual adjustments, making it ideal for handling large datasets efficiently.
After installing Kutools for Excel, follow these steps to use Convert between Text and Number.
- Select the range containing the numbers with leading apostrophes, and click Kutools > Content > Convert between Text and Number.
- In the Convert between Text and Number dialog box, select Text to number, and click OK.
Result
The leading apostrophes are removed from the selected numbers immediately.
Download Kutools for Excel and start your free trial now!
Remove leading apostrophes with Text to Columns
Excel's Text to Columns feature can also remove leading apostrophes from a continuous column of text-formatted numbers.
- Select the column of numbers from which you want to remove the leading apostrophes.
- Click Data > Text to Columns. In the Convert Text to Columns Wizard, click Finish directly.
- The leading apostrophes are removed, and the text-formatted numbers are converted into numeric values.
📝 Note:
Text to Columns processes one column at a time. If the selected data spans several columns, repeat the process for each column.
Remove leading apostrophes with VBA
For a more automated approach, the following VBA code converts numeric text in a selected range into true numbers, removing the leading apostrophes at the same time.
- Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
- Click Insert > Module, and paste the following code into the module window:
VBA code: Remove leading apostrophes from numbers
Sub RemoveLeadingApostrophes() Dim rng As Range Dim WorkRng As Range Dim xTitleId As String On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox( _ "Select the range to convert", _ xTitleId, WorkRng.Address, Type:=8) On Error GoTo 0 If WorkRng Is Nothing Then Exit Sub For Each rng In WorkRng If Not rng.HasFormula Then If VarType(rng.Value) = vbString And IsNumeric(rng.Value) Then rng.Value = CDbl(rng.Value) End If End If Next rng End Sub - Press F5 to run the code. In the prompt box, select the range you want to convert.
- Click OK. The leading apostrophes are removed from numeric text in the selected cells.
📝 Note:
The macro changes the selected cells directly. Save a backup first and use a macro-enabled .xlsm workbook if you want to retain the code.
Frequently asked questions
Why does an apostrophe appear in the formula bar but not in the cell?
A leading apostrophe tells Excel to treat the entry as text. It acts as an input prefix, so it is normally hidden in the worksheet cell.
Why doesn't changing the cell format to Number remove the apostrophe?
Changing the number format affects only how a value is displayed; it does not convert an existing text value into a number. Use one of the conversion methods in this article.
Will converting the entries remove leading zeros?
Yes. Numeric values do not retain insignificant leading zeros. Values such as ZIP codes, account numbers, or IDs may need to remain as text.
Why does the VALUE formula return #VALUE!?
The source may contain spaces, letters, or other characters that Excel cannot interpret as part of a number. Clean the source text before converting it.
Which method is best for several columns or repeated conversions?
Kutools can process a selected range directly. VBA is useful when the same conversion needs to be repeated regularly, while Text to Columns must be run one column at a time.
Effectively removing leading apostrophes from numbers in Excel ensures that your data is not only correct but also usable for calculations and analysis. Each method provided in this article offers unique advantages to suit different needs. By incorporating these techniques into your Excel toolkit, you can streamline data preparation and improve the reliability of your results. For those eager to delve deeper into Excel's capabilities, our website offers a wealth of tutorials. Discover more Excel tips and tricks here.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in