How to Remove Spaces from Imported Text and Convert to Numbers in Spreadsheet
Question details
The user needs to clean up imported data by removing regular or nonbreaking spaces from numerical text (e.g., 1 359,48) to convert them into standard usable numbers.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Importing numerical data from external sources, such as web pages or databases, where thousand separators appear as spaces, causing the system to format them as text instead of numbers.
- Observed behavior
- The imported numbers are treated as text strings because of the embedded spaces, preventing mathematical calculations or data analysis from functioning correctly.
Before modifying your data, it is recommended to format the target cells as 'General' or 'Number' to ensure the cleaned text converts correctly without default text formatting.
Use Find and Replace to Remove Regular Spaces
The fastest method to convert text to numbers when standard spaces are used as thousand separators.
This built-in tool is ideal for quickly stripping out normal spaces across thousands of rows of data simultaneously.
Highlight the specific column or range of cells containing the imported text values.
Press the keyboard shortcut Ctrl+H to immediately open the Find and Replace dialog box.
Enter a single space in the 'Find what' field, leave the 'Replace with' field completely empty, and click the 'Replace All' button.
Use Formulas to Remove Nonbreaking Spaces
If Find and Replace does not remove the spaces, your data likely contains nonbreaking spaces (often from web imports) which require a specific function.
Easily Convert Text to Numbers in WPS Spreadsheet
WPS Spreadsheet offers an intuitive interface and advanced functions designed to clean up messy imported data effortlessly. Process thousands of rows of data with zero lag.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing your imported text data.
- 2. Select the target column: Click the column header to highlight all cells with the unwanted spaces.
- 3. Apply Find & Replace: Press Ctrl+H, type a space in 'Find what', leave 'Replace with' blank, and hit 'Replace All'.
- 4. Perform Calculations: Your data is instantly converted to actual numbers, ready for SUM, AVERAGE, and other formulas.
Frequently Asked Questions
Why didn't Find and Replace remove the spaces from my imported numbers?
When data is copied from web pages or specific external databases, spaces are often formatted as 'nonbreaking spaces' (character code 160). A regular Find and Replace looks for standard spaces (character code 32), so it ignores them. You must use the SUBSTITUTE function with CHAR(160) to remove them.
How do I fix issues with commas and decimal points after removing spaces?
If your imported text uses a comma as a decimal separator (e.g., 1 359,48) but your regional computer settings use a period, the conversion to numbers may fail. You can nest another SUBSTITUTE function to replace the comma with a period before using the VALUE function.
Is there a way to permanently replace the formula results with raw numbers?
Yes. Once you have used the SUBSTITUTE formula to clean your numbers, select the formula column, press Ctrl+C to copy, then right-click the same selection and choose 'Paste Special' > 'Values'. This removes the underlying formulas and leaves only the pure numerical data.
