Preventing TextToColumns Trimming Leading Spaces

[Stephen2]

Hi,

I am trying to programatically use TextToColumns via VB 6.

I would like to use TextToColumns, however, when I split it trims
trailing spaces.

Can I turn this off somehow?



Thanks in advance,

Stephen

 

[David McRitchie]

Hi Stephen,

If you edit your data beforehand you could change " " to "~"

 

[Stephen2]

Yes, I have thought of that solution, but it is NOT clean at all.

Replacing all characters with a character that possibly exists in m
data is not a solution.

Then, after all the splitting, I would have to replace (in every cell
the ~ with " ", perhaps taking a long time, and not being efficient a
all.

Thank you for the attempt, but this is not a solution.


Stephen

 

[David McRitchie]

Hi Stephen,
The replacement afterwards would go very fast because you would
do a replace for the entire range with one instruction. Yes it is not
clean, but having to use Text to Columns is a compromise in itself,
and you do not have control over it.

You are changing the file extension to .txt so you have the most
control.

If you want/need more control you can take a look at Chip Pearson's
Importing From Text Files
http://www.cpearson.com/excel/imptext.htm#Import

If you were going to have to do each cell individually for some things
you would at least limit to the used range, and special cells is by
definition limited to the used range. So it is not as bad as it sounds.
http://www.mvps.org/dmcritchie/excel/proper.htm#specialcells