Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel autofilter asterisk
- Thread starter cathyg18
- Start date
1. You cannot.
2. Asterisks are seen as wildcard characters in Excel.
3. If I were you, I would come up with some other way to designate those values. Like cell or font colors.
4. To get yourself out of your bind, select all the cells, copy and paste to Word. Do a find/replace. Find the asterisk and replace with what you've chosen to replace it. Click inside the table and hit Table-Select table, then copy and paste back into Excel.
Hope this helps! Anne Troy
2. Asterisks are seen as wildcard characters in Excel.
3. If I were you, I would come up with some other way to designate those values. Like cell or font colors.
4. To get yourself out of your bind, select all the cells, copy and paste to Word. Do a find/replace. Find the asterisk and replace with what you've chosen to replace it. Click inside the table and hit Table-Select table, then copy and paste back into Excel.
Hope this helps! Anne Troy
I don't understand. What does your data look like? I just tried it and had no problem with Auto-Filter, (Custom...) and selecting contains for the "what" and a single * in the right-hand combo box.
Here is my test data:
[/b]
You did say "autofilter" in your subject.
Of course, if you really meant "Find" then I believe Dreamboat is correct. But you can still set an auto-filter, then copy (or put a mark in an adjacent cell) to get access to the cells that contain an asterisk.
Here is my test data:
Code:
THINGS
Code:
1
2
3
*
*
1
2
3
1*3
13*You did say "autofilter" in your subject.
Of course, if you really meant "Find" then I believe Dreamboat is correct. But you can still set an auto-filter, then copy (or put a mark in an adjacent cell) to get access to the cells that contain an asterisk.
- Thread starter
- #5
My data is text, some of which have an asterisk at the end. Yes, I do mean autofilter. When I select 'contains' for the 'what' and put a single asterisk in the right hand box, all my data is retrieved. I'm guessing that text acts differently than numbers in this case.
Any ideas?
Any ideas?
Fascinating! With text, all cells are selected, as you said.
Apparently, specifying "Contains" automatically filters out numbers and then proceeds to use the asterisk as a wild card on the remaining text cells.
Just goes to show how faulty test data can lead to an erroneous conclusion.
If you can use a work column, you can identify the cells with an asterisk with this formula:
FIND( ) is case-sensitive and does not allow wild-card characters.
Apparently, specifying "Contains" automatically filters out numbers and then proceeds to use the asterisk as a wild card on the remaining text cells.
Just goes to show how faulty test data can lead to an erroneous conclusion.
If you can use a work column, you can identify the cells with an asterisk with this formula:
Code:
=IF(ISERROR(FIND("*",A2)),"","Asterisk")- Thread starter
- #7
This works for me. You did say that the * is at the end?
In a spare column, put this in the cell level with the top of your column
=right(cellone,1) 'replace cellone with the cell at the top of your column
copy and paste this down as far as necessary. Now autofilter on the new column and * should be one of the options. I've tested this in '97 on both General and Text formats.
bandit600
In a spare column, put this in the cell level with the top of your column
=right(cellone,1) 'replace cellone with the cell at the top of your column
copy and paste this down as far as necessary. Now autofilter on the new column and * should be one of the options. I've tested this in '97 on both General and Text formats.
bandit600
- Status