Thread: What does TrailingMinusNumbers do in the TextToColumns Method

Hi,
I made a function to convert text to columns which is working fine in Excel
- Office 2003. In excel office 2000 it does not work, and I found out it is
the TrailingMinusNumbers :=True statement that causes the error. So my
question is... what is this function supposed to do. There is not a lot of
documentation that I can fid so anyone who knows, please clarify.
thanks in advance
Lennard

Lennard -

In some output from older (but still in use) mainframe accounting
systems, the minus sign is placed after the number, not before. In the
old days (~Excel 97) there were lots of discussions here about how to
put the minus sign back in front of the number.

Apperently TrailingMinusNumbers was a recent enhancement to the
TextToColumns routine. When using the wizard in Excel's UI, it is
accessed from the Advanced button in Step 3, and by default is True.

If you don't have to worry about trailing minus signs, remove this
optional parameter; the macro recorder must have helpfully included it
for you.

The documentation is absolutely asinine for this parameter, stating that
it tells Excel that the characters trailing the minus sign are numeric
if true or text if false. Who wrote that???

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Lennard van Putten wrote:

> Hi,
> I made a function to convert text to columns which is working fine in Excel
> - Office 2003. In excel office 2000 it does not work, and I found out it is
> the TrailingMinusNumbers :=True statement that causes the error. So my
> question is... what is this function supposed to do. There is not a lot of
> documentation that I can fid so anyone who knows, please clarify.
> thanks in advance
> Lennard

Hi Jon,

thanks for the clarifications. This makes a lot more sense now. In my usage
there will not be minus signs after the value so it is not applicable to me.
I agree that the help text is not useful. Someone must have been multi
tasking when writing that.

Cheers.

"Jon Peltier" wrote:

> Lennard -
>
> In some output from older (but still in use) mainframe accounting
> systems, the minus sign is placed after the number, not before. In the
> old days (~Excel 97) there were lots of discussions here about how to
> put the minus sign back in front of the number.
>
> Apperently TrailingMinusNumbers was a recent enhancement to the
> TextToColumns routine. When using the wizard in Excel's UI, it is
> accessed from the Advanced button in Step 3, and by default is True.
>
> If you don't have to worry about trailing minus signs, remove this
> optional parameter; the macro recorder must have helpfully included it
> for you.
>
> The documentation is absolutely asinine for this parameter, stating that
> it tells Excel that the characters trailing the minus sign are numeric
> if true or text if false. Who wrote that???
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Lennard van Putten wrote:
>
> > Hi,
> > I made a function to convert text to columns which is working fine in Excel
> > - Office 2003. In excel office 2000 it does not work, and I found out it is
> > the TrailingMinusNumbers :=True statement that causes the error. So my
> > question is... what is this function supposed to do. There is not a lot of
> > documentation that I can fid so anyone who knows, please clarify.
> > thanks in advance
> > Lennard
>

Some people can't write and chew gum at the same time. Anyway, I've
submitted the poor help descriptions to Microsoft for correction.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Lennard van Putten wrote:
> Hi Jon,
>
> thanks for the clarifications. This makes a lot more sense now. In my usage
> there will not be minus signs after the value so it is not applicable to me.
> I agree that the help text is not useful. Someone must have been multi
> tasking when writing that.
>
> Cheers.
>
> "Jon Peltier" wrote:
>
>
>>Lennard -
>>
>>In some output from older (but still in use) mainframe accounting
>>systems, the minus sign is placed after the number, not before. In the
>>old days (~Excel 97) there were lots of discussions here about how to
>>put the minus sign back in front of the number.
>>
>>Apperently TrailingMinusNumbers was a recent enhancement to the
>>TextToColumns routine. When using the wizard in Excel's UI, it is
>>accessed from the Advanced button in Step 3, and by default is True.
>>
>>If you don't have to worry about trailing minus signs, remove this
>>optional parameter; the macro recorder must have helpfully included it
>>for you.
>>
>>The documentation is absolutely asinine for this parameter, stating that
>>it tells Excel that the characters trailing the minus sign are numeric
>>if true or text if false. Who wrote that???
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>Lennard van Putten wrote:
>>
>>
>>>Hi,
>>>I made a function to convert text to columns which is working fine in Excel
>>>- Office 2003. In excel office 2000 it does not work, and I found out it is
>>>the TrailingMinusNumbers :=True statement that causes the error. So my
>>>question is... what is this function supposed to do. There is not a lot of
>>>documentation that I can fid so anyone who knows, please clarify.
>>>thanks in advance
>>>Lennard
>>