Data Import & Export

How to Fix Excel Currency Formatting for Imported Numbers

Maira Mehtab Sep 27, 2026 869 views

Question details

Users are unable to apply Currency or Accounting formatting to numbers imported into Excel because the data is incorrectly stored as text.

Product: Spreadsheets
Device & OS: not provided
Scenario: Importing external data from other software or databases into a spreadsheet where numeric values require financial formatting.
Observed behavior: The Currency or Accounting formatting fails to apply to the imported data, either showing no change or missing the currency symbols because the values are recognized as text strings instead of numbers.

Before you start

Check if your imported numbers align to the left side of the cell by default or have a small green triangle in the top-left corner, as both indicate the numbers are stored as text.

Solution 1Recommended

Convert Text to Numbers Using Text to Columns

This is the fastest method to convert an entire column of text-formatted imported numbers into actual numbers so currency formatting can be applied.

The Text to Columns wizard forces Excel to re-evaluate the data in the selected cells, instantly converting text strings that look like numbers into functional numeric values.

Select the Data

Highlight the entire column of imported numbers that are failing to format properly.

Open Text to Columns

Navigate to the Data tab on the top ribbon and click on 'Text to Columns'.

Finish the Conversion

Without changing any settings in the wizard, simply click the 'Finish' button.

Apply Currency Formatting

Select the column again, go to the Home tab, and choose 'Currency' or 'Accounting' from the Number Format dropdown.

Quick Formatting: Once converted, any future formatting changes applied to these cells will update immediately.

Solution 2

Force Number Conversion with Paste Special

A clever workaround using the Paste Special tool to multiply text values by 1, forcing the spreadsheet to recognize them as numeric data.

Solution 3

Use the VALUE Function

Ideal if you need to keep the original imported data untouched while generating clean numeric values in a separate column.

Smart Data Processing

Format Imported Financial Data Easily with WPS Spreadsheet

WPS Office Spreadsheet provides intuitive error-checking tools that automatically detect text-formatted numbers from external imports, allowing you to instantly convert and format them as currency in bulk.

1. Open your File in WPS: Launch WPS Spreadsheet and open the file containing your imported data.
2. Highlight the Errors: Select the cells containing the numbers. Look for the small green warning triangle in the upper left corner of the cells.
3. Convert to Number: Click the yellow warning icon that appears next to your selection and choose 'Convert to Number' from the dropdown menu.
4. Apply Financial Formatting: Right-click the selected cells, choose 'Format Cells', navigate to the 'Number' tab, and select 'Currency' or 'Accounting'.

Automatically identifies and flags numbers incorrectly stored as textFully compatible with Microsoft Excel (.xlsx and .csv) formatsBulk convert entire columns from text to numbers instantlyAdvanced, localized currency and accounting formatting options

microsoft office alternative - wps office

Frequently Asked Questions

Why do my imported numbers have a green triangle in the corner?

Spreadsheet applications use a small green triangle as an error indicator to warn you that a number is currently stored as text. Clicking the warning icon beside it allows you to quickly convert it to a usable numeric value.

What is the difference between Currency and Accounting formats?

The Currency format places the currency symbol directly next to the number. The Accounting format aligns the currency symbols and decimal points perfectly in a column for easier reading, and it displays zero values as dashes.

Why does only the currency symbol disappear when importing data?

This usually happens if the source system exports financial symbols as unrecognized text characters. You may need to use the Find and Replace feature to remove the static text symbol before applying the built-in numeric currency format.

Can I automatically format imported CSV files as numbers?

Yes. When opening CSV files, you can use the Text Import Wizard to specify the data formats for each column. Setting the column format to 'General' instead of 'Text' usually prevents financial formatting issues from occurring.

Excel & Data Problems