When importing text field Excel chops off leading spaces

[septimus]

I need to import text files into Excel. These files have a field that
contains a long series of letters, representing student responses to
test questions. When I import these as Fixed Width data, putting in
the break points manually, Excel chops all leading spaces off of those
long series of letters. In other words, " BCD DA" becomes "BCD DA".

Obviously, this makes it appear that the student answered the first
two questions "BC" when really she failed to answer those first two
questions.

This happens regardless of whether I designate that column as a
General or Text field.

When I import the text file into Access, I don't have this problem. Is
there any way to fix it in Excel?

Thanks.

 

[Ron Rosenfeld]

I need to import text files into Excel. These files have a field that
contains a long series of letters, representing student responses to
test questions. When I import these as Fixed Width data, putting in
the break points manually, Excel chops all leading spaces off of those
long series of letters. In other words, " BCD DA" becomes "BCD DA".

Obviously, this makes it appear that the student answered the first
two questions "BC" when really she failed to answer those first two
questions.

This happens regardless of whether I designate that column as a
General or Text field.

When I import the text file into Access, I don't have this problem. Is
there any way to fix it in Excel?

Thanks.

It is annoying behavior, for sure.

One way: before running the wizard, use Find/Replace to replace all the <space>'s with some other character that does not occur normally in your data (e.g. tilde, ampersand, CHAR(1),etc). Then run the wizard with the columns designated to be TEXT. Then use Find/Replace again to change those characters back to <space>'s.

 

[septimus]

Hm. That does seem to work. Maybe not the most elegant solution in the
world, but it does appear to get the job done. Thanks!

 

[Ron Rosenfeld]

Hm. That does seem to work. Maybe not the most elegant solution in the
world, but it does appear to get the job done. Thanks!

Glad to help. Since the method seems to work, you could probably simplify things using a macro.

By the way, when I import the data "delimited", the leading spaces seem to be preserved. But you probably know that.