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.
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))
- Press Ctrl + Enter.
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.