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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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?
 

ctlw83

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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

use an advanced filter instead...
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

ctlw83

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

Forum statistics

Threads
1,141,019
Messages
5,703,776
Members
421,315
Latest member
awaisnazir139

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
Top