Format Imported Excel Numbers With Commas and Decimal Places
Question details
The user needs to convert a raw imported number string into a properly formatted number featuring thousands separators (commas) and two decimal places.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing raw numerical data that lacks proper decimal and comma formatting, often representing cents or raw unit values.
- Observed behavior
- The imported numbers appear as continuous strings of digits (e.g., 81812008481) instead of formatted values (e.g., 818,120,084.81).
Before applying formatting, verify if your imported numbers represent whole units or cents. If they represent cents, you will need to divide the numbers by 100 to calculate the correct decimals.
Use a Helper Column to Divide by 100 and Apply Format
Best for when the imported data represents cents or smaller fractional units that need to be converted to a standard decimal format.
When raw financial data is exported from certain databases, it is often stored in cents to avoid decimal data types. By using a quick formula in a helper column, you can restore the correct decimal placement before styling it.
Select an empty cell next to your imported value (for example, B1). Type =A1/100 (assuming your imported value is in cell A1) and press Enter.
Right-click the newly calculated cell and select 'Format Cells' from the context menu, or press Ctrl+1 on your keyboard.
Go to the 'Number' tab. Set 'Decimal places' to 2, and check the box that says 'Use 1000 Separator (,)'. Click OK to apply the formatting.
Apply Number Formatting Directly to Whole Units
Use this method if your imported numbers are already in the correct whole units but are simply missing commas and decimals.
Format Your Imported Data Effortlessly with WPS Office
WPS Spreadsheet provides highly intuitive tools for formatting raw imported data, whether you need thousands separators, decimal points, or custom currency symbols. It handles large datasets smoothly and uses identical formatting menus and shortcuts to Microsoft Excel.
- 1. Open your data: Launch WPS Spreadsheet and open the file containing your raw imported numbers.
- 2. Convert values if necessary: If the numbers are in cents, create a new column and divide the raw value by 100.
- 3. Format cells: Select the target cells, right-click, and choose 'Format Cells' (or press Ctrl+1).
- 4. Apply styling: Under the Number tab, check the 1000 separator box, set decimal places to 2, and click OK.
Frequently Asked Questions
Why do my imported numbers have leading zeros that disappear?
When importing data, spreadsheet software automatically interprets numeric strings as standard numbers and drops leading zeros. To keep them, import the column as text or format the destination cells as text before pasting the data.
How can I format an entire column with commas and decimals at once?
Click the column letter header at the top to select the entire column, then use the shortcut Ctrl+1 to open the Format Cells dialog. From there, select the Number category, set 2 decimal places, and check the thousands separator box.
What if my imported data is formatted as text and cannot be calculated?
If numbers are stored as text, they will not calculate and often display a small green triangle in the corner of the cell. Highlight the affected cells, click the yellow warning icon that appears next to them, and choose 'Convert to Number' before applying your formatting.
