Converting text to numbers and vice versa in Excel is crucial for efficient data management. When you convert text to numbers, you enable accurate calculations and data processing, which is essential for tasks like financial reporting and statistical analysis. On the other hand, converting numbers to text can be beneficial for formatting outputs, creating clear and readable labels, and presenting data in a more user-friendly manner.
In this article, you will learn how to convert text to numbers and numbers to text in Excel using Spire.XLS for Python.
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Convert Text to Numbers in Excel
If you import data from another source into Excel, a small green triangle may appear in the upper-left corner of the cell. This error indicator indicates that the number is stored as text. Numbers that are stored as text can cause unexpected results, like an uncalculated formula showing instead of a result.
To convert numbers stored as text to numbers, you can simply use the CellRange.ConvertToNumber() method. The CellRange object can represent a single cell or a range of cells.
The steps to convert text to numbers in Excel are as follows:
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet using Workbook.Worksheets[index] property.
- Get a cell or a range of cells using Worksheet.Range property.
- Convert the text in the cell(s) into numbers using CellRange.ConvertToNumber() method.
- Save the document to a different Excel file.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Get a cell range
range = worksheet.Range["D2:G13"]
# Convert text to number
range.ConvertToNumber()
# Save the workbook to a different Excel file
workbook.SaveToFile("output/TextToNumbers.xlsx", ExcelVersion.Version2013)
# Dispose resources
workbook.Dispose()
Convert Numbers to Text in Excel
When working with numerical data in Excel, you might encounter situations where you need to convert numbers to text. This is particularly important when dealing with data that requires specific formatting, such as IDs or phone numbers that must retain leading zeros.
To convert the number in a cell into text, you can set the CellRange.NumberFormat to @. The CellRange object represents a single cell or a range of cells.
The detailed steps to convert numbers to text in Excel are as follows:
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet using Workbook.Worksheets[index] property.
- Get a specific cell or a range of cells using Worksheet.Range property.
- Convert the numbers in the cell(s) into text by setting CellRange.NumberFormat to @.
- Save the document to a different Excel file.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Employee.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Get a cell range
cellRange = worksheet.Range["F2:F9"]
# Convert numbers in the cell range to text
cellRange.NumberFormat = "@"
# Save the workbook to a different Excel file
workbook.SaveToFile("output/NumbersToText.xlsx", ExcelVersion.Version2013)
# Dispose resources
workbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
