Hi, Jim!
I would appreciate your assistance with removing unwanted spaces
(Excel 2001),
when retrieving one column of numbers from the internet, using
Copy-Paste.
(These numbers are two place decimals, and are already in
Tab-Delimited columns).
i.e.:
5.3
.35
1.65
.83
etc.
To date, I have been deleting the unwanted spaces, one cell at a time.
There 'must' be a quicker way than that! Right?
I want Excel to ignore all spaces 'after' these numbers, as I Paste!
I went to Microsoft's Knowledge Base:
............................................................................................................................................
Method 4: Remove Hidden Characters and Spaces
This method works best if the data is arranged in a single column or
row. This technique uses the TRIM function and the CLEAN function to
remove extra spaces and nonprinting characters that may be imported
with the file. The following example assumes that the data is in
column A and starts in row 1 ($A$1). To use this example, follow these
steps:
1. Insert a column to the right of column A by selecting column B and
clicking Columns on the Insert menu.
2. In the first cell of the inserted column (B1), type the following:
$B$1: =VALUE(TRIM(CLEAN(A1)))
3. In column B, select all the cells to the right of the cells that
contain data in column A.
4. On the Edit menu, point to Fill, and then click Down.
The new column contains the values of the text in column A.
5. With the same range selected, click Copy on the Edit menu.
6. Select cell A1, and click Paste Special on the Edit menu. Under
Paste, click Values, and click OK to paste the converted values back
on top of column A.
7. Delete column B by selecting the column and clicking Delete on the
Edit menu.
The text that was in column A is in a number format.
.............................................................................................................................................
This didn't work!
Numbers in my Column A, that were automatically placed by Excel as
left aligned
(due to spaces after the number, and thus treated by Excel as text )
returned: #VALUE!
So, I tried:
............................................................................................................................................
Method 6: Use Text to Columns
This method works best if the data is arranged in a single column. The
following example assumes that the data is in column A and starts in
row 1 ($A$1). To use this example, follow these steps:
1. Select one column of cells that contain the text.
2. On the Data menu, click Text to Columns.
3. Under Original data type, click Delimited, and click Next.
4. Under Delimiters, click to select the Tab check box, and click
Next.
5. Under Column data format, click General.
6. Click Advanced and make any appropriate settings for the Decimal
separator and Thousands separator. Click OK.
7. Click Finish.
The text is converted to numbers.
..........................................................................................................................................
This didn't do anything to the numbers, or the spaces! No change!
Just changing the format to General, or Number, doesn't work either!
Thanks for your help!
Fred
