Filter not working right

ctlw83

New Member
Joined
Sep 5, 2006
Messages
6
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Chris

Since 51371162 is a number I don't think you can use begins with.

Wouldn't it be better to have the 2 numbers in different cells, or use some sort of seperator between them?
 
Upvote 0
Actually, the list wasn't made by me, and separating the over 1000 number entries might be a bit daunting. It seems to work fine with another drill log we have set up in the same fasion....
 
Upvote 0
Hi,ctlw83
Welcome to the Board !!!!!
separating the over 1000 number entries might be a bit daunting
nobody said you would have to do this one by one, formulas can be used ...
what would be an option for you to change the data ?
  • using two cells
    insert dash between both numbers
    adding a symbol before each item

if you don't want this
another way to filter would be
"larger than" 51370000
"smaller than or equal to" 51379999

kind regards,
Erik
 
Upvote 0
Hi Chris

I don't know if it's an option to you, but if you convert the numbers in that column to text you'll have no problem in using the "begins with".

Hope this helps
PGC
 
Upvote 0
Hi Chris

I don't know if it's an option to you, but if you convert the numbers in that column to text you'll have no problem in using the "begins with".

Hope this helps
PGC
Hi, pgc01,

did you try that ?
I did before posting and just tried again, but it didn't work for me
(office XP)

kind regards,
Erik
 
Upvote 0
Hi Erik

Yes, I tried it.

The first time it didn't work because I just formatted the column as text.
Then I remembered that it couldn't work because format does not change the value. So I used text to columns to convert the numbers to text and it worked with no problem.

Cheers
PGC
 
Upvote 0
Hi, guys,

since ctlw83 has 2 only 2 posts by now, I can imagine my questions are his

pgc01,
I used text to columns to convert the numbers to text
text to columns: all the time it is told here we use it as one of the tricks to convert text into numbers (which is the opposite of what you are writing)
I really tried your suggestion: how did you do that ??

PaddyD,
use an advanced filter instead...
perhaps I'm loosing my brain tonight :unsure:
can you elaborate this ?

if you prefer to wait for ctlw83's reply, I can understand ... :)

kind regards,
Erik
 
Upvote 0
Umm, yeah, no clue about converting numbers to text...not to mention I am not sure if you mean spelling out the numbers or just converting them so the program "sees" them as text...
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top