Data imported from other sources into Excel often requires reformatting. However, text that is preceded or followed by spaces cannot be reformatted in the usual way. If you try to convert such text to numbers using Excel’s Format Cells command, the errors remain. You need to trim the spaces before you can convert the data.
For example, say you’ve imported the numerical data in column A, shown in Figure A. It is formatted as text, so you need to change it to number format before you can work with it. One method is to click on the Smart Tag, as shown, and select Convert To Number. Excel converts the contents in the cells to numbers.
Figure A
Another method is to use Excel’s Value and Trim functions. Follow these steps:
- Select C1:C10.
- Enter the following formula (Figure B):
=VALUE(TRIM(A1))
Figure B
- Press Ctrl + Enter.
Excel displays the numerical values for each cell. You can now copy the results to another part of the workbook using the Paste Special option to copy the values without the formulas (Figure C).
Figure C
Miss an Excel tip?
Check out the Microsoft Excel archive and catch up on other Excel tips.
Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.
