CSV file Imported to Excel

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

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

Another option is to open a blank excel sheet. Go to Data > Import External Data, Import Data. Then choose the file. Once it opens the file in the wizard, you can use the formating option per cell to format the cells before importing. This was Excel does not mess with the data/change the data.

I also run into this issue where Excel likes to change certain things to dates, Scientific, or whatever and screw up the info, so formatting as text solves this problem as Excel will import it as text and not mess with the original formatting.

Just a thought.

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
>