Skip to main content

Add leading apostrophe to a list of numbers in Excel

Author: Xiaoyang Last Modified: 2024-07-04
doc insert leading apostrophe 1

In the realm of data analysis and management, identifying and counting duplicate values in a dataset is a crucial task. Excel, provides several methods to count duplicate values in a column efficiently. This article explores various techniques to accomplish this, from simple functions to more advanced features, helping you choose the best approach based on your dataset size and specific requirements.

Why Use an Apostrophe in Excel?

Preserve Leading Zeros:
Excel automatically removes leading zeros from numbers because it treats them as insignificant in numerical values. By converting numbers to text, all leading zeros are preserved.

Prevent Automatic Formatting:
Excel might automatically format numbers in ways you don't intend, such as turning large numbers into scientific notation. Using an apostrophe prevents this.

Data Integrity:
Ensuring that the data is entered and stored exactly as intended, especially when it will be used for imports or exports to other software.

Insert / Add apostrophe in front of numbers with VBA code

The following simple code may help you to insert apostrophe before each number in a selection, please do as follows:

1. Select the range of numbers that you want to add the apostrophe.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Then click Insert > Module, and paste the following code in the Module window.

VBA code: Insert apostrophe in front of numbers at once:

Sub Addapostrophe()
'updateby Extendoffice 20151207
For Each cell In Selection
cell.Value = "'" & cell.Value
Next cell
End Sub

4. After pasting the code, then press F5 key to run this code, and the apostrophe has been inserted in front of the numbers at once.

doc insert leading apostrophe 1  2 doc insert leading apostrophe 2

Insert / Add apostrophe in front of numbers with Kutools for Excel

If you have Kutools for Excel’s Convert between Text and Number utility, you can quickly convert the numbers to text format with the leading apostrophe.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Enhanced with AI capabilities, Kutools automates tasks with precision, making data management effortless. Read More...         Free trial...

After installing Kutools for Excel, please do as this:

1. Select the numbers that you want to add the leading apostrophe.

2. Click Kutools > Content > Convert between Text and Number, see screenshot:

3. In the Convert between Text and Number dialog box, please select Number to text, and click Ok or Apply, and the apostrophe has been added in front of the numbers as following screenshot shown:

doc insert leading apostrophe 4

Using an apostrophe to manage numeric data as text in Excel is a simple yet powerful way to maintain the integrity of your data. Whether you’re working with IDs, codes, or sensitive numeric data, this approach ensures that your numbers remain exactly as you input them, preserving their intended format and significance. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials. Please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!

Demo: Insert / Add apostrophe in front of numbers

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Related articles:

How to remove leading apostrophe from numbers in Excel?

How to add / insert certain character every x characters into cells?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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!
Comments (5)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
="'"&N4....N4 being the Cell, notice its " then ' the " again. Regards
This comment was minimized by the moderator on the site
First one worked! Thank you!
Rated 5 out of 5
This comment was minimized by the moderator on the site
Alt+Shift+''text'' to Excel 360, OK.
This comment was minimized by the moderator on the site
This saved my life
This comment was minimized by the moderator on the site
Used the "Insert / Add apostrophe in front of numbers with VBA code" and worked like a charm.
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations