In addition to what Dave Peterson suggested, if you enter 13 spaces in the
"Find What" box in the Find and Replace window then it won't replace all the
single "good" spaces, if you have them, just the string of 13. You can also
check Match entire cell contents under Options in the Find and Replace
window. This won't get any cells with only 12 spaces though.

Also, Excel will still potentially not see that cell that you replaced
spaces with blanks as an actual blank cell. (I wish I knew why.) Not a big
deal unless you use formulas with ISBLANK() or COUNTA(), etc.



"Dave Peterson" wrote:

> I think I'd try to fix the problem before it got to excel. Maybe you could go
> back to the sender and ask them to change the way they create the file.
>
> If that's not possible, then (maybe) just open the file and do edit|replace.
> Replace a space character with (leave blank).
>
> If you have actual words in any of the fields that are separated by spaces, this
> is not a good idea.
>
> But maybe you could just select the range to change first, then do the mass
> change.
>
> If worse came to worse, you could always cycle through the usedrange.
>
> dim myCell as range
> for each mycell in activesheet.usedrange
> if trim(mycell.value) = "" then
> mycell.clearcontents
> end if
> next mycell
>
> ===
> Or just run David McRitchie's TrimAll (that may fix other problems as well):
> http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> (look for "Sub Trimall()")
>
> JMay wrote:
> >
> > I rec'd a CSV file today @ work. From Excel I did a File, Open and it opened
> > immediately (without the File Open Wizard which I was hoping for). Anyway,
> > everything LOOKED fine in the Range of numbers between B2 and K30 - with 20 or
> > 30 blank cell among the numbers; I later
> > found out the blank cells WERE NOT Blank, but rather had 13 hard-coded space
> > characters in them.
> >
> > I later opend the file in Word Pad and saw how that it appeared something like
> > as follows:
> >
> > 4344.56, 3232.56, , 555.55,
> >
> > Where in excel it was:
> > B C D E
> > 2 4,344.56 3,232.56 "13spaces" 555.55
> >
> > This situation SHOULD be corrected before it gets to the end user, but what
> > should be done to eliminate the fields creating the 13 spaces versus a null
> > string ""?
> > Thanks in advance,,
> > Jim
>
> --
>
> Dave Peterson
>