count of tab characters in a single cell

[Guest]

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

 

[Bernard Liengme]

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

 

[Guest]

Looking at A9:

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

will display the number of tab characters

 

[Guest]

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.

 

[Guest]

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]

Chip at www.cpearson.com has a macro for identifying/removing special
characters.
good luck!