Data Import & Export

How to Convert an Exported Table to an Editable Excel Worksheet

Maira Mehtab Sep 27, 2026 871 views

Question details

The user needs to convert a table exported from another program so it can be edited and used for calculations, as current formulas are returning zero due to text formatting.

Product: Spreadsheet
Device & OS: not provided
Scenario: Analyzing and modifying data that has been exported from a third-party software or database.
Observed behavior: Imported numerical values are being treated as text because currency symbols (like "$") remain in the data, causing calculation formulas to return zero or fail completely.

Before you start

Before modifying your data, check if the numbers are aligned to the left side of the cells, which is a strong indicator that the spreadsheet is treating them as text rather than numerical values.

Solution 1Recommended

Remove Currency Symbols Using the SUBSTITUTE Formula

Use a combination of the SUBSTITUTE function and a double unary operator to strip out text characters like dollar signs and force the remaining string into a calculable numerical value.

When data is exported from external programs, currency symbols and spaces often cause numbers to be stored as text. The SUBSTITUTE function can target and remove specific characters.

Insert a helper column

Add a new blank column immediately adjacent to the data you need to convert.

Apply the SUBSTITUTE formula

In the first blank cell of the new column, enter the formula =--SUBSTITUTE(A1, "$", "") (assuming your raw data is in cell A1). This will replace the dollar sign with nothing.

Copy the formula down

Select the cell with the formula, click and hold the small square at the bottom-right corner (fill handle), and drag it down to apply the formula to the rest of the column.

Paste as values

Copy the entire column of newly calculated numbers. Right-click the original text-formatted column, select "Paste Special", and choose "Values" to permanently replace the messy data with clean numbers.

Double Unary Operator: The two minus signs (--) at the beginning of the formula act as a double unary operator. It multiplies the stripped text string by -1 twice, forcing the spreadsheet to convert it back into an actual number.

Solution 2

Clean Data with Find and Replace

A quick, batch method to clean the entire worksheet without writing formulas is to use the Find and Replace tool to eliminate unwanted symbols.

Seamless Data Processing with WPS Office

Easily Convert and Calculate Exported Data in WPS Spreadsheet

WPS Spreadsheet offers intuitive tools for importing, cleaning, and calculating data exported from third-party systems. With robust text-to-columns features and full Excel formula support, handling messy data takes just a few clicks.

1. Open the exported file: Launch WPS Spreadsheet, click "Open", and select your exported data file to load it into the workspace.
2. Clean the text data: Use the shortcut Ctrl + H to batch remove unwanted currency symbols, or utilize the =--SUBSTITUTE() formula to extract pure numbers dynamically.
3. Verify your calculations: Input your required sum or calculation formulas to test the converted data. The formulas will now process the data accurately without returning zero.

Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Advanced data processing tools like Text to Columns and powerful Find & Replace.Comprehensive support for hundreds of calculation functions, including SUBSTITUTE.Lightweight, fast, and free to use across Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel formulas return zero on an exported worksheet?

Exported files frequently save numbers as text strings, particularly if they contain currency symbols or special spacing. Because spreadsheet formulas cannot calculate text, they default to returning a zero or generating a #VALUE! error.

Can I use the Text to Columns feature to fix text-formatted numbers?

Yes. Select the problematic column, navigate to the Data tab, click "Text to Columns", and simply click "Finish" without altering any steps. This forces the application to re-evaluate the text as actual numbers.

How do I remove hidden or non-breaking spaces in exported data?

You can use the =TRIM(A1) function to eliminate regular spaces. For non-breaking spaces commonly found in web-exported tables, use the formula =VALUE(SUBSTITUTE(A1, CHAR(160), "")).

How do I fix formulas that show as text instead of the calculated result?

If you see the formula text (like =SUM(A1:A10)) instead of the result, ensure the cell format is not set to "Text". Change the format to "General" or "Number", then double-click the cell and press Enter to recalculate.

Excel & Data Problems