count of tab characters in a single cell

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),""))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mark" <[email protected]> wrote in message
news:[email protected]...
> 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

Looking at A9:

=LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),""))

will display the number of tab characters

--
Gary''s Student


"mark" wrote:

> 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

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.

"Gary''s Student" wrote:

> Looking at A9:
>
> =LEN(A9)-LEN(SUBSTITUTE(A9,CHAR(9),""))
>
> will display the number of tab characters

Thanks... I've never used the substitute function was the problem (why I
didn't think of it).. but this will work great.



"Bernard Liengme" wrote:

> =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(9),""))
> best wishes
> --

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

"mark" <[email protected]> wrote in message
news:[email protected]...
> 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