Filter not working right

[ctlw83]

I set up an autofilter on a sheet for logging drill holes for a seismic drill company so I could easilly browse and search the sheet. However, the "begins with" in the custom part of the filter doesn't seem to work.

for instance, a hole is broken up into 2 numbers, the line number and the hole number

Line 5137, hole 1162

so the whole number is 51371162

Now I need to enter that hole as logged but, there are also other holes beginning with 5137 that I need to log today, but when I search for begins with 5137 the entire sheet comes up blank. Any suggestions?

God Bless,
Chris

 

[PaddyD]

review the excel help file for 'Filter by using advanced criteria', in particular 'Conditions created as the result of a formula'.

In this case:

Book4
	A	B	C	D
1	ID
2	51371		TRUE
3	51372
4	51111
5	111
6	5137123456
Sheet1

...using the range c1:c2 as the range for the filter criteria, list would be filtered as required.

 

[pgc01]

Hi again

To convert the numbers in a column to text:

- Select the column (or a column vector)
- Data>Text to columns
- Choose delimited in the first panel. Click next
- Do nothing in the second panel. Click next
- In the third panel in the "Column data format" choose Text. Click Finish.

You can check that it's really text f. ex. with the ISTEXT function.

Try the autofilter with the "begins with".

Hope it's clear
PGC

 

[erik.van.geit]

it's not "my" thread, but Thanks, pgc and paddyD, that's clear
indeed loosing my brain a bit: will go offline ASAP

 

[ctlw83]

I looked up some information on the text to column feature but, don't understand how this would help me in this case, especially because there is nothing separating the 8 numbers.

I'm just wondering if this shouldn't work with numbers like these, then why does the function work on our list for our other job?

But, any simple suggestions are welcome at this point....

 

[pgc01]

Hi ctlw83

Did you try my last post? Do this small test:

Write in a column some of your 8 digit numbers. Follow the steps in my last post. Set the autofilter in that column. Try the "begins with".

HTH
PGC

 

[ctlw83]

will try that....didn't see the new post....

 

[ctlw83]

YES! That worked. Ok, thank you very much for all the help. I know where to go if I ever need excel help again, which I probably will....