Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count of tab characters in a single cell
=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(9),""))
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "mark" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count of tab characters in a single cell
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count of tab characters in a single cell
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count of tab characters in a single cell
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 -- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count of tab characters in a single cell
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set number of characters within a cell in Excel 2003? | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
How to copy single cell into cell that is merged from two cells? | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) |