Java - Convert Excel Cell Value from Text to Number or Number to Text
Tags: java, excel, number, text
Published: 2022-10-09 08:02:38

When processing Excel files, you may find that some cell values look like numbers, but they are actually stored as text. If you use these cells in Excel functions like SUM and Average, you will get incorrect calculation results because these functions ignore cells that contain text values. In this case, you need to convert the numbers stored as text back to general numbers. This article will explain how to **convert Excel cell value from text to number or number to text in Java** using [Free Spire.XLS for Java](https://www.e-iceblue.com/Introduce/free-xls-for-java.html) API.

## Add Dependencies
**Method 1:** If you are using maven, you can easily import the JAR file of Free Spire.XLS for Java API into your application by adding the following code to your project's pom.xml file.
```java
<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.free</artifactId>
        <version>5.1.0</version>
    </dependency>
</dependencies>
```
**Method 2:** If you are not using maven, you can download Free Spire.XLS for Java from the [official website](https://www.e-iceblue.com/Download/xls-for-java-free.html), extract the zip file, then import the Spire.Xls.jar file under the lib folder into your project as a dependency.

## Convert Excel Cell Value from Text to Number in Java
Free Spire.XLS for Java provides the **convertToNumber()** method of the **CellRange** class to convert number values formatted as text back to numbers. The following are the detailed steps to do so: 

- Initialize an instance of the **Workbook** class.
- Load an Excel file using **Workbook.loadFromFile()** method.
- Get the desired worksheet using **Workbook.getWorksheets().get(int)** method.
- Access the specific cell range that contains numbers stored as text using **Worksheet.getCellRange(int, int, int, int)** method.
- Call the **CellRange.convertToNumber()** method to convert the numbers stored as text back to numbers.
- Save the result file using **Workbook.saveToFile(String, ExcelVersion)** method.
```java
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ConvertTextToNumber {
    public static void main(String []args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample1.xlsx");
        
        //Get the first worksheet 
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Get the specific cell range that contains numbers stored as text
        CellRange range = sheet.getCellRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
        //Convert numbers stored as text back to number
        range.convertToNumber();
        
        //Save the result file
        workbook.saveToFile("ConvertTextToNumber.xlsx", ExcelVersion.Version2016);
    }
}
```

![Convert Excel Cell Value from Text to Number in Java](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8b7xfturd58zm5c1be7c.png)
## Convert Excel Cell Value from Number to Text in Java
You can format the numbers in Excel cells as text, decimals, dates etc. using the **setNumberFormat()** method of the **CellRange** class. The following steps show you how to format numbers in Excel cells as text:

- Initialize an instance of the **Workbook** class.
- Load an Excel file using **Workbook.loadFromFile()** method.
- Get the desired worksheet using **Workbook.getWorksheets().get(int)** method.
- Access the specific cell range that contains numbers using **Worksheet.getCellRange(int, int, int, int)** method.
- Call the **CellRange.setNumberFormat(String)** method to convert the numbers to text.
- Save the result file using **Workbook.saveToFile()** method.
```java
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ConvertNumberToText {
    public static void main(String []args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample2.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Get the specific cell range that contains numbers
        CellRange range = sheet.getCellRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
        //Convert numbers to text
        range.setNumberFormat("@");

        //Save the result file
        workbook.saveToFile("ConvertNumberToText.xlsx", ExcelVersion.Version2016);
    }
}
``` 

![Convert Excel Cell Value from Text to Number in Java](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/so9ze9y0tpi1rlmopkgd.png)