software

Hi.

I have an application that I wrote a while ago that is having a little bit
of problem with bogus data being given to it.

The input is a tab separated variable file, where every record of data
should have the same number of tab characters.

I'm thinking of inputting the file all into one column, and then counting
the number of tab characters in a given cell, to check if all of the input
records are valid. My guess is that one of the users is going in and
manipulating the text file after it comes from it's source, and manually
changing a tab to spaces, etc.

The tab character can be identified by the function code(9) .

My question is this, can someone help me come up with a formula that will
count the number of tab characters in a given cell's content?

I could work it into a loop, but it would run much faster if I can just do
it with a count type function, but I haven't seen the one I need, yet.

Thanks.
Mark

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(9),quot;quot;))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;markquot; gt; wrote in message
...
gt; Hi.
gt;
gt; I have an application that I wrote a while ago that is having a little bit
gt; of problem with bogus data being given to it.
gt;
gt; The input is a tab separated variable file, where every record of data
gt; should have the same number of tab characters.
gt;
gt; I'm thinking of inputting the file all into one column, and then counting
gt; the number of tab characters in a given cell, to check if all of the input
gt; records are valid. My guess is that one of the users is going in and
gt; manipulating the text file after it comes from it's source, and manually
gt; changing a tab to spaces, etc.
gt;
gt; The tab character can be identified by the function code(9) .
gt;
gt; My question is this, can someone help me come up with a formula that will
gt; count the number of tab characters in a given cell's content?
gt;
gt; I could work it into a loop, but it would run much faster if I can just do
gt; it with a count type function, but I haven't seen the one I need, yet.
gt;
gt; Thanks.
gt; Mark
Looking at A9:

=LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),quot;quot;))

will display the number of tab characters

--
Gary''s Studentquot;markquot; wrote:

gt; Hi.
gt;
gt; I have an application that I wrote a while ago that is having a little bit
gt; of problem with bogus data being given to it.
gt;
gt; The input is a tab separated variable file, where every record of data
gt; should have the same number of tab characters.
gt;
gt; I'm thinking of inputting the file all into one column, and then counting
gt; the number of tab characters in a given cell, to check if all of the input
gt; records are valid. My guess is that one of the users is going in and
gt; manipulating the text file after it comes from it's source, and manually
gt; changing a tab to spaces, etc.
gt;
gt; The tab character can be identified by the function code(9) .
gt;
gt; My question is this, can someone help me come up with a formula that will
gt; count the number of tab characters in a given cell's content?
gt;
gt; I could work it into a loop, but it would run much faster if I can just do
gt; it with a count type function, but I haven't seen the one I need, yet.
gt;
gt; Thanks.
gt; Mark

Thanks... exactly what I was looking for.

Of coure, now that you've told me that, I'm finding that there might be more
problems with the file than I thought, but hey... that's what I need to know!

Thanks.

quot;Gary''s Studentquot; wrote:

gt; Looking at A9:
gt;
gt; =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),quot;quot;))
gt;
gt; will display the number of tab charactersThanks... I've never used the substitute function was the problem (why I
didn't think of it).. but this will work great.
quot;Bernard Liengmequot; wrote:

gt; =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(9),quot;quot;))
gt; best wishes
gt; --

Chip at www.cpearson.com has a macro for identifying/removing special
characters.
good luck!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;markquot; gt; wrote in message
...
gt; Hi.
gt;
gt; I have an application that I wrote a while ago that is having a little bit
gt; of problem with bogus data being given to it.
gt;
gt; The input is a tab separated variable file, where every record of data
gt; should have the same number of tab characters.
gt;
gt; I'm thinking of inputting the file all into one column, and then counting
gt; the number of tab characters in a given cell, to check if all of the input
gt; records are valid. My guess is that one of the users is going in and
gt; manipulating the text file after it comes from it's source, and manually
gt; changing a tab to spaces, etc.
gt;
gt; The tab character can be identified by the function code(9) .
gt;
gt; My question is this, can someone help me come up with a formula that will
gt; count the number of tab characters in a given cell's content?
gt;
gt; I could work it into a loop, but it would run much faster if I can just do
gt; it with a count type function, but I haven't seen the one I need, yet.
gt;
gt; Thanks.
gt; Mark