How to Remove Leading Apostrophe in Excel [10 Ways]

Do you want to remove a leading apostrophe in Excel?

This can be a frustrating task, especially if you do not know the right steps to take. In this blog post, you will learn how to remove a leading apostrophe in Excel using functions and tools.

You may notice that the number or date treat as text in excel when you copy data from other sources to excel.

In the excel application if you type the integer number in a cell. Excel considers that as a number format. Also, if you enter a number such as 12/03/23 or 12-03-23 into an Excel spreadsheet, it will automatically recognize it as a date and format it as such. This is because Excel recognizes the / slash and - hyphen symbols as indicative of a date format.

To avoid that Excel users used to start with an apostrophe when they need to treat it as text format in excel. The apostrophe can only be seen in the formula bar when selecting the cell, and otherwise stays invisible.

Suppose you use ActiveX controls in an excel project, the linked cell stores the entered value as text. You have to convert the value to a number for further analysis.

You have Excel in-built functions VALUE, NUMBERVALUE, and DATEVALUE to convert a text to a number. You can use Mathematical Operator to remove leading apostrophes in excel.  Excel commands Paste Special, Text to Columns, Error Checking and Flash Fill helps to remove the leading apostrophes.

Power Query or Power Pivot tools help to convert the text into a number in excel. VBA or Office Scripts code can use in your automation to remove the leading apostrophes.

In this article, you are going to learn to remove the leading apostrophe with the below sample data. The user form Employee Details has used two types of ActiveX Control Text Box and Combo Box, which are linked to the cells F19, F20, and F21.

Get your copy of the example workbook used in this blog post to follow along!

Remove Leading Apostrophe with Excel Functions

Excel VALUE() or NUMBERVALUE() functions convert the given string that represents a number to a number format. If the text represents a date, you can use DATEVALUE() function to convert to a number, which returns the excel date-time code.

VALUE( text )

Go to cell B2, type the formula =VALUE( A2 ) , and Press Enter. Drag the fill handle to the range B2:B6 to copy the formula.

NUMBERVALUE( Text, [Decimal_separator], [Group_separator] )

Type the formula =NUMBERVALUE( E2 ) in cell F2 and Press Enter. Press Ctrl + D keyboard shortcut to copy the F2 cell content to the selected cell F3.

DATEVALUE( text )

Type the formula =DATEVALUE( E4 ) in cell F4 and Press Enter. Press Ctrl + D keyboard shortcut to copy the F4 cell content to the selected cell F5.

Remove Leading Apostrophe in Excel with Flash Fill

Flash Fill is a new feature in Excel. It is a big-time saver when it comes to data entry, and automatically completes data entry tasks based on patterns that you enter.

You can start entering the number in cells B2 and B3. Excel will give you the pattern based on your entries in cells B2 and B3. You just press Enter to fill in the values.

Remove Leading Apostrophe in Excel with Paste Special

Paste Special command in Excel allows you to paste only certain attributes of copied data, such as its formatting, value, formula, comment, etc. It also gives the option to paste data from other sources such as a Word document or an external program. You can see several options in the paste special dialog box.

Copy the range of cells A2:A6, Go to cell B2, Select the Paste command in the Home Tab, and then choose Paste Special… to open the Paste Special dialog box.

Select the options Values in the Paste section, Add in the Operation section and then Press OK.

Remove Leading Apostrophe in Excel with Excel Error Checking

Error Checking feature helps you to identify and fix errors in your worksheets. Excel is used to scan the worksheet data for any potential errors, such as incorrect formulas, incorrect data types, and circular references. You can see a small green triangle in the cell If any errors are found in the cell. Select the cell and expand the warning icon drop-down to see the suggestions.

Select the range of cells A2:A6. You can see a warning icon displayed next to the active cell. Click the warning icon and then Choose the Convert to Number option to convert the selected range of cell values to numbers.

Remove Leading Apostrophe in Excel with Mathematical Operations

Mathematical Operators are symbols or functions used to compute mathematical operations, addition, subtraction, multiplication, division, and exponentiation. These operators generally take two operands and output a single result.

If you multiply or divide a number by the value 1. The output will be the same number no changes in the value. 

Select the cell B2, type the formula =A2 * 1 alternatively you can use the formula =A2 / 1, and Press Enter. Drag the fill handle to the range B2:B6 to copy the formula.

Remove Leading Apostrophe in Excel with Text to Columns

The Excel Text to Columns command is used to split a single column of data into two or more columns based on a delimiter such as a comma, space, tab, etc.,

Select the range of cells A2:A6 and then choose the Text to Columns command in the Data Tab, to open the Convert Text to Columns Wizard. 

Select the Delimited option in the first step and press Next. 

Make sure the Other check box is selected, type the apostrophe in the input box, and press Finish.

Remove Leading Apostrophe in Excel with Power Pivot

Excel Power Pivot is a data analysis and modeling tool to create complex models, such as pivot tables, charts, and reports. Power Pivot allows you to create relationships between data sets, calculated columns, and measures.

Select the range of cells A1:A6 and press the command Add to Data Model in the Power Pivot tab.

In the Power Pivot editor, select the second column, type the formula =Value( [Data from Source] ) , and press Enter.

Rename the column header Convert to Number and select the Flattened PivotTable option in the PivotTable command.

You have options to select the New Worksheet or Existing worksheet and select the worksheet location.  Once you press the OK button, Excel adds an empty pivot table in the worksheet.  You can drag the Convert to Number field in the Rows Area and see your data converted as numbers.

Remove Leading Apostrophe in Excel with Power Query

Excel Power Query is a useful tool for data processing and transformation. It provides to connect to different data sources. Also allows you to clean, transform, process, and format the connected data for your reports and analysis. This saves your time and formats your data for analysis.

Steps to convert text to numbers using Power Query

1. Select the range of cells A1:A6 in the PowerQuery Sheet
2. Choose From Table/Range option in Data Tab – to open the Power Query window.

3. Expand the Data Type: dropdown options in the Home Tab
4. Select the Whole Number option.

5. Select the Close & Load command and Press Close & Load To… – to popup Import Data dialog box.

6. In the Import Data dialog box, select the Table option, and provide the location in the  Existing worksheet to insert the transformed Power Query table into your worksheet.

Remove Leading Apostrophe in Excel with VBA

VBA is a programming language that allows users to automate repetitive tasks, create custom formulas, and generate reports in Microsoft Excel. Using the below VBA script, you can easily remove the leading apostrophe in the data.

Sub RemoveLeadingApostrophe()
'Declare variable
Dim selectedRange As Range

'Assign worksheet and selected range to the respective variables
Set selectedRange = ActiveSheet.Range(Selection.Address)

'loop through the range of cells
For Each rg In selectedRange
      rg.Offset(0, 1).Value = rg.Value * 1
Next
End Sub

Insert the above code in your VBE module.

Macro declares the variable and assigns the currently selected range to the variable selectedRange. Loop through the range of cells, multiply the current cell value by 1 and assign it to the adjacent cell.

Steps to Run the RemoveLeadingApostrophe macro.

1. Select the range of cells A2:A6
2. Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
3. Choose the RemoveLeadingApostrophe Macro and then Press Run.

Remove Leading Apostrophe in Excel with Office Scripts

Office Scripts is a scripting language that enables users to create automated tasks and macros in Excel, both in the offline version of Excel and its web version. With Office Scripts, users can create custom formulas and manipulate data. Office Scripts also makes it easier to collaborate with colleagues and keep worksheets up to date with automated scripts.

function main(workbook: ExcelScript.Workbook) {

    //getselected range
    let rng = workbook.getSelectedRange();
    let rows = rng.getRowCount();

    //loop through the selected range of cells
    for (let i = 0; i < rows; i++) {

        //Assign the cell value to the variable cellValue as string
        let cellValue = rng.getCell(i, 0).getValue() 
        rng.getCell(i, 1).setValue(cellValue)

    }
}

Office Scripts assigns the selected range to the variable rng. The next step script loop through each cell in the selected range, assign the current cell value to the variable cellValue, and set the value to the adjacent cell.

Follow the below steps to execute the convertText2Num Office script in your excel

1. Select the range of cells A2:A6
2. Go to Automate menu.
3. Select the convertText2Num Script – to open the Code editor on the right side of your excel application.
4. Press Run 

Conclusions

Adding an apostrophe in front of numbers, date, or formula in excel make that become text format. The leading apostrophe force the excel application to treat the cell content as a text value. Even if the cell contains a number, date, or formula starting with an equal sign.

In this article, you learned all the different ways to remove the leading apostrophe in excel.

Use the Excel function VALUE or NUMBERVALUE to remove leading apostrophe for further analysis. DATEVALUE function converts the text to a number, and it represents the excel date time code.

If you like to work with keyboard shortcuts you can use excel commands Paste Special, Flash Fill, Text to Columns, and Error Checking methods. When you deal with a large data set you can try Power Query or Power Pivot approach.  VBA or Office scripts help to remove leading apostrophe in your report automation or dashboards.

Do you know any other method to Remove Leading Apostrophe in Excel? Let me know in the comments section!