ok excel wizards... need some help

**rush2049** · 04-24-12, 08:00 AM

OK so say I have a column that has data like these three lines:

ND 16, 43, 89, 116, 22, 1, 6
or
JG 34, 785,223 , 16, 2316, 116
or
por45,89,16,116, NGR 643, PO 166

How would I filter said column that has thousands of rows to exclude anywhere there is a 16... but I still want to see 116 and 166's and things containing 16, but not 16 itself.....

I realize its terribly difficult, and no the formatting of said cell's aren't always consistent as shown by example 3....

edit: I was messing with the filter-custome filter- thing.... but the wildcard or rather lack of wildcard/ regular expressions ruined that feature....

**Menoske** · 04-24-12, 08:05 AM

If 16 is the only item in that cell, and equals 16 filter should work. Equals is an exact match so it shouldn't filter out 116 or 166. Granted the sorting function in excel sucks hardcore sometimes.

Sent from my PC36100 using Tapatalk 2

**RhysJD3** · 04-24-12, 08:07 AM

Are all the numbers in a single cell? So is 16, 43,89,116,22,1,6 in one cell?

**rush2049** · 04-24-12, 08:07 AM

I realize that, but I specifically gave 3 lines of data.... each line is a single cell in said hypothetical column.....

**Menoske** · 04-24-12, 08:08 AM

What version of Excel are you using....there is a large difference in what 2003 can do when compared to 2007 or 2010. If the above suggestion doesn't work you may need to build some kind of work around by adding a column and doing a if statement with either the match or find parameter to see if its just 16 or whatever and then use that new column to sort. If you need more help ill gladly take a look at yer spreadsheet and help ya out.

Sent from my PC36100 using Tapatalk 2

**rush2049** · 04-24-12, 08:11 AM

I am using 2010. I also exported this data from access, if access has anything that would be more helpful..... data is in the same format both places....

I can't give you the spread sheet..... sensitive info. If you really want a sheet as an example I can make a blank one and post those three cells in the first column and send it.... but its rather a lot of work when I feel I clearly explained my issue.

**rush2049** · 04-24-12, 09:00 AM

Ok, thanks for the quick replies, but I found a solution..... I have to make a macro-enabled sheet and then use this little ditty to get my regular expressions.....
Regular Expressions in MS Access

Then I am set....

edit: after adding the above macro, then I used this to search for what I wanted:
=regexp([@FixBy],"([^0-9]|^)16([^0-9]|$)")

where FixBy is my column name and 16 is what I am searching for.....
Then I just filter to only include the 0's found in that column.