How to eliminate spaces in imported data?

Posted by Pankaj Lad on September 25, 2000 10:08 PM

I am copy data from a website. After pasting it into excel 2000, I noticed that my column of data containing dates looked like this: " 09/01/2000 ".

Notice the extra spaces before and after the date. These little spaces prevent excel from recognizing this data as dates.

Does any way have any ideas on how to fix this? Writing a macro would be ideal. I've already tried the obvious tricks. . . nothing seems to work except manually removing the spaces. . . this is too time consuming. . .

PLEASE HELP!!

Posted by David on September 26, 2000 1:41 AM

Here is something realy quick. It is ok if it is a one or two time thing but if the size of your data varies and you do it a lot you might want to get the macro to define the rows and columns itself. Let me know if you need more.

Sub trimspaces()
numcol = 100
numRows = 100
For col = 1 To numcol
For Row = 1 To numRows
Cells(Row, col) = trim(Cells(Row, col))
Next Row
Next col
End Sub

Posted by Pankaj on September 28, 2000 6:21 PM

David,

I tried your suggestion; unfortunately, it does not work. To see for yourself, type " 03/03/2000 " in cell A1 (make sure to include the spaces . . .of course omitting the quotes).

If I type that particular string inside the trim function it works. . .like this:
Cells(Row, col) = Trim(" 03/03/2000 ").
How can I save the contents of the current cell as a string variable and then use that value inside the trim function?

Thanks in advance.

Posted by Tim Francis-Wright on September 28, 2000 7:19 PM

I tried your suggestion; unfortunately, it does not work. To see for yourself, type " 03/03/2000 " in cell A1 (make sure to include the spaces . . .of course omitting the quotes). If I type that particular string inside the trim function it works. . .like this:

I have found that the DATEVALUE worksheet
function does an excellent job of turning
date-formatted text into date values. It
assumes that the text is in the default date
format (and will use the current year if the
year is omitted). For some reason, however, it
generates a #VALUE! error when confronted with
an actual date.

It should prove useful here because it ignores
any and all extraneous spaces from the
relevant text. See the online help for
more details.

HTH

Posted by Chas on October 08, 2000 7:26 AM

We have a similar problem daily when we import in
to a worksheet from our business system. Our fix
was to do a find and replace for the entire sheet
(just for formatting reasons). We find all "space"
"space" ( ) and replace with "blank" ()and then
on the critical formula data, we select just the
column with that info and do a find and replace
single "space" and replace with "blank". Takes
just a second to do. Maybe you already tried it,
but it works for us.