Convert Numbers to Text or Numeric Text to Numbers in Excel Using Java

In Excel, numbers may be stored as text, which can interfere with calculations, sorting, and data analysis. Converting between numbers and text is a common task when managing Excel data.

This article demonstrates how to perform these conversions in Java, including turning numbers into text and converting numeric text back into numeric values.

Install an Excel Processing Library

Before getting started, you need to include a library that supports Excel file operations. This guide uses Spire.XLS for Java, which allows you to read, modify, save, and convert Excel files directly in Java.

Step 1: Add Spire.XLS for Java to Your Project

You can add Spire.XLS for Java via Maven by including the following in your pom.xml:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>15.11.3</version>
    </dependency>
</dependencies>

Step 2: Import Required Classes

Once the dependency is added, import the Spire.XLS classes in your Java file:

import com.spire.xls.*;

Part 1: Converting Numbers to Text in Excel

Sometimes, you may need to convert numeric values to text format. This is particularly useful when you want to preserve formatting or display numbers in a specific style.

Why Convert Numbers to Text?

Common scenarios include:

• Preserving leading zeros: Important for phone numbers, postal codes, or product codes. Converting numbers to text ensures leading zeros are retained.

• Displaying in a specific format: You might want numbers displayed as currency, percentages, or in a custom format.

• Concatenating with text: Converting numbers to text ensures smooth concatenation with other text, e.g., “Product 123”.

How to Convert Numbers to Text Using Java

The setNumberFormat("@") method allows you to convert numbers to text in Excel. The "@" symbol represents text format in Excel.

Step 1: Load the Excel File

import com.spire.xls.*;

public class ConvertNumberToText {

    public static void main(String[] args) {
        // Create a Workbook instance
        Workbook workbook = new Workbook();

        // Load the Excel file
        workbook.loadFromFile("Sample.xlsx");

        // Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);
    }
}

Step 2: Convert Numbers to Text

// Convert numbers in the range A2:A10 to text format
worksheet.getRange().get("A2:A10").setNumberFormat("@");

Step 3: Save the Modified File

// Save the modified Excel file
workbook.saveToFile("NumbersToText.xlsx", ExcelVersion.Version2013);
workbook.dispose();

Complete Code Example

import com.spire.xls.*;

public class ConvertNumberToText {

    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Convert numbers to text
        worksheet.getRange().get("A2:A10").setNumberFormat("@");

        // Save the modified file
        workbook.saveToFile("NumbersToText.xlsx", ExcelVersion.Version2013);
        workbook.dispose();

        System.out.println("Numbers have been successfully converted to text. File saved as NumbersToText.xlsx");
    }
}

Part 2: Converting Numeric Text to Numbers in Excel

Sometimes numbers are stored as numeric text in Excel, preventing them from being used in calculations. Converting numeric text to actual numbers allows you to perform arithmetic operations such as addition, subtraction, multiplication, and division.

Why Convert Numeric Text to Numbers?

• Enable calculations: Numeric text cannot be used in formulas.

Correct sorting: Numeric text sorts lexicographically, which may produce incorrect order.

• Data cleaning: Ensures all numbers are stored in numeric format to avoid issues in further analysis.

How to Convert Numeric Text to Numbers Using Java

Spire.XLS for Java provides a convenient convertToNumber() method to convert numeric text to numeric values.

Step 1: Load the Excel File

import com.spire.xls.*;

public class ConvertNumericTextToNumber {

    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");
        Worksheet worksheet = workbook.getWorksheets().get(0);
    }
}

Step 2: Convert Numeric Text to Actual Numbers

// Convert numeric text in range D2:D8 to numeric values
worksheet.getRange().get("D2:D8").convertToNumber();

Step 3: Save the Modified File

// Save the file
workbook.saveToFile("NumericTextToNumbers.xlsx", ExcelVersion.Version2013);
workbook.dispose();

Complete Code Example

import com.spire.xls.*;

public class ConvertNumericTextToNumber {

    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Convert numeric text to numeric values
        worksheet.getRange().get("D2:D8").convertToNumber();

        // Save the file
        workbook.saveToFile("NumericTextToNumbers.xlsx", ExcelVersion.Version2013);
        workbook.dispose();

        System.out.println("Numeric text has been successfully converted to numbers. File saved as NumericTextToNumbers.xlsx");
    }
}

Conclusion

This guide demonstrated how to perform two common Excel data conversions using Java:

• Convert numbers to text – useful for preserving formatting and handling text concatenation.

• Convert numeric text to numeric values – essential for calculations, sorting, and data cleaning.

These operations not only help clean and format Excel data but also ensure accurate results in subsequent computations, analysis, and sorting.