| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
All data disappears when using autofilter
This one has me truly beat. I have a long list of numbers, and I need to filter the ones out that have a zero as the second digit from the left. I have been using auto filtercustom and tried using wild cards, and have had no success. I have included some sample data below. You can also find a copy of the actual xls sheet at www.ideaharvesters.com/testxls.xls Account 00616946 03745254 03745254 10043826 13635685 20071601 20560611 20560611 20560611 23474810 30437875 -- apandbp ------------------------------------------------------------------------ apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118775 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
All data disappears when using autofilter
So just use a helper column. In cell E2 put this and copy down:
=MID(TEXT(A2,"00000000"),2,1)="0" Now pop in a header and filter on TRUE or FALSE depending on what you want. Your problem stems from the fact that your data is set to display the number 616946 as 00616946, so what you see isn't actually what you have, so you will find it hard to filter on those 0s when they don't exist. The TEXT function will simply ensure that what you actually see is returned as a value, and then the MID function simply allows you to focus on that second character. Note it is now TEXT so hence the quotes round the 0. Regards Ken........................ "apandbp" wrote in message ... This one has me truly beat. I have a long list of numbers, and I need to filter the ones out that have a zero as the second digit from the left. I have been using auto filtercustom and tried using wild cards, and have had no success. I have included some sample data below. You can also find a copy of the actual xls sheet at www.ideaharvesters.com/testxls.xls Account 00616946 03745254 03745254 10043826 13635685 20071601 20560611 20560611 20560611 23474810 30437875 -- apandbp ------------------------------------------------------------------------ apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118775 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
All data disappears when using autofilter
Sorry, make that D2 not E2 - Didn't even see your formulas in there.
Regards Ken..................... "Ken Wright" wrote in message ... So just use a helper column. In cell E2 put this and copy down: =MID(TEXT(A2,"00000000"),2,1)="0" Now pop in a header and filter on TRUE or FALSE depending on what you want. Your problem stems from the fact that your data is set to display the number 616946 as 00616946, so what you see isn't actually what you have, so you will find it hard to filter on those 0s when they don't exist. The TEXT function will simply ensure that what you actually see is returned as a value, and then the MID function simply allows you to focus on that second character. Note it is now TEXT so hence the quotes round the 0. Regards Ken........................ "apandbp" wrote in message ... This one has me truly beat. I have a long list of numbers, and I need to filter the ones out that have a zero as the second digit from the left. I have been using auto filtercustom and tried using wild cards, and have had no success. I have included some sample data below. You can also find a copy of the actual xls sheet at www.ideaharvesters.com/testxls.xls Account 00616946 03745254 03745254 10043826 13635685 20071601 20560611 20560611 20560611 23474810 30437875 -- apandbp ------------------------------------------------------------------------ apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118775 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
All data disappears when using autofilter
WOW! That is truly amazing. It worked perfectly. Thank you so much for your help. Would you mind explaining briefly what exactly that formula (=MID(TEXT(A2,"00000000"),2,1)="0") does? Kindest Regards, Anthony Ken Wright;427495 Wrote: So just use a helper column. In cell E2 put this and copy down: =MID(TEXT(A2,"00000000"),2,1)="0" Now pop in a header and filter on TRUE or FALSE depending on what you want. Your problem stems from the fact that your data is set to display the number 616946 as 00616946, so what you see isn't actually what you have, so you will find it hard to filter on those 0s when they don't exist. The TEXT function will simply ensure that what you actually see is returned as a value, and then the MID function simply allows you to focus on that second character. Note it is now TEXT so hence the quotes round the 0. Regards Ken........................ "apandbp" wrote in message ... This one has me truly beat. I have a long list of numbers, and I need to filter the ones out that have a zero as the second digit from the left. I have been using auto filtercustom and tried using wild cards, and have had no success. I have included some sample data below. You can also find a copy of the actual xls sheet at www.ideaharvesters.com/testxls.xls Account 00616946 03745254 03745254 10043826 13635685 20071601 20560611 20560611 20560611 23474810 30437875 -- apandbp ------------------------------------------------------------------------ apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp' (http://www.thecodecage.com/forumz/member.php?userid=550) View this thread: 'All data disappears when using autofilter - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=118775) -- apandbp ------------------------------------------------------------------------ apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118775 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
All data disappears when using autofilter
The functions MID and TEXT are both standard Excel functions, and their
syntax and operation can be found in the Excel help for the function concerned. TEXT(...,"00000000") turns the number 616946 into a text string 00616946. MID(...,2,1) takes one character, starting at the second character of the 8 character string, so it extracts the second 0. The formula then checks whether that character is indeed 0, and returns TRUE if it is, or FALSE if it isn't. -- David Biddulph "apandbp" wrote in message ... WOW! That is truly amazing. It worked perfectly. Thank you so much for your help. Would you mind explaining briefly what exactly that formula (=MID(TEXT(A2,"00000000"),2,1)="0") does? Kindest Regards, Anthony Ken Wright;427495 Wrote: So just use a helper column. In cell E2 put this and copy down: =MID(TEXT(A2,"00000000"),2,1)="0" Now pop in a header and filter on TRUE or FALSE depending on what you want. Your problem stems from the fact that your data is set to display the number 616946 as 00616946, so what you see isn't actually what you have, so you will find it hard to filter on those 0s when they don't exist. The TEXT function will simply ensure that what you actually see is returned as a value, and then the MID function simply allows you to focus on that second character. Note it is now TEXT so hence the quotes round the 0. Regards Ken........................ "apandbp" wrote in message ... This one has me truly beat. I have a long list of numbers, and I need to filter the ones out that have a zero as the second digit from the left. I have been using auto filtercustom and tried using wild cards, and have had no success. I have included some sample data below. You can also find a copy of the actual xls sheet at www.ideaharvesters.com/testxls.xls Account 00616946 03745254 03745254 10043826 13635685 20071601 20560611 20560611 20560611 23474810 30437875 -- apandbp ------------------------------------------------------------------------ apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp' (http://www.thecodecage.com/forumz/member.php?userid=550) View this thread: 'All data disappears when using autofilter - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=118775) -- apandbp ------------------------------------------------------------------------ apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118775 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
All data disappears when using autofilter
to me wild cards worked perfectly
just enter your data as text and then <in filter try : " equals " ?0* hole will help -- Basilio "apandbp" wrote: WOW! That is truly amazing. It worked perfectly. Thank you so much for your help. Would you mind explaining briefly what exactly that formula (=MID(TEXT(A2,"00000000"),2,1)="0") does? Kindest Regards, Anthony Ken Wright;427495 Wrote: So just use a helper column. In cell E2 put this and copy down: =MID(TEXT(A2,"00000000"),2,1)="0" Now pop in a header and filter on TRUE or FALSE depending on what you want. Your problem stems from the fact that your data is set to display the number 616946 as 00616946, so what you see isn't actually what you have, so you will find it hard to filter on those 0s when they don't exist. The TEXT function will simply ensure that what you actually see is returned as a value, and then the MID function simply allows you to focus on that second character. Note it is now TEXT so hence the quotes round the 0. Regards Ken........................ "apandbp" wrote in message ... This one has me truly beat. I have a long list of numbers, and I need to filter the ones out that have a zero as the second digit from the left. I have been using auto filtercustom and tried using wild cards, and have had no success. I have included some sample data below. You can also find a copy of the actual xls sheet at www.ideaharvesters.com/testxls.xls Account 00616946 03745254 03745254 10043826 13635685 20071601 20560611 20560611 20560611 23474810 30437875 -- apandbp ------------------------------------------------------------------------ apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp' (http://www.thecodecage.com/forumz/member.php?userid=550) View this thread: 'All data disappears when using autofilter - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=118775) -- apandbp ------------------------------------------------------------------------ apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=118775 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
All data disappears when using autofilter
Of course if you read the rest of the replies you will realise that he does
not actually have the data he thinks he has, so no wildcard is going to help as it cannot find what is not actually there to begin with, so hence the use of the TEXT function to actually give him what he already thought he had, after which any of his normal filtering methods should now work. "basilio" wrote in message ... to me wild cards worked perfectly just enter your data as text and then <in filter try : " equals " ?0* hole will help -- Basilio |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Disappears When Closed | Excel Worksheet Functions | |||
| data label disappears when I move it | Charts and Charting in Excel | |||
| Data disappears!! | Excel Discussion (Misc queries) | |||
| F2 error: New data disappears when hit enter | Excel Worksheet Functions | |||
| cell shows all the data, click out of it, end of data disappears | Excel Discussion (Misc queries) | |||
Hybrid Mode
