Digital filter?
Solved
ManuMatane
Posted messages
6
Status
Member
-
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention -
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention -
Hello everyone,
The responses to a survey are sent to me in csv format.
I transfer this data into Excel so that a third-party user (who knows nothing about it) can easily extract the information they need using command buttons associated with macros.
One of my columns corresponds to the student number (7 digits), and the cell is formatted as a number with 0 decimal places.
I want my colleague to be able to input a student number and output the entire row corresponding to that student. A filter does the job, but I only have the text filter available and not the numerical filter for my column.
I can't find the trick to force the filter to recognize the numbers. Out of curiosity, I changed a student number to a code made up of letters and my filter works perfectly.
Thank you for your wise advice!
Manu
The responses to a survey are sent to me in csv format.
I transfer this data into Excel so that a third-party user (who knows nothing about it) can easily extract the information they need using command buttons associated with macros.
One of my columns corresponds to the student number (7 digits), and the cell is formatted as a number with 0 decimal places.
I want my colleague to be able to input a student number and output the entire row corresponding to that student. A filter does the job, but I only have the text filter available and not the numerical filter for my column.
I can't find the trick to force the filter to recognize the numbers. Out of curiosity, I changed a student number to a code made up of letters and my filter works perfectly.
Thank you for your wise advice!
Manu
2 answers
Hello
if you don't have a numeric filter in the menu, it’s probably because although your cells are in number format, the values remain as text.
See if this manipulation can help you:
if the codes display on the left, it means they are text
you can (maybe, but not sure) "digitize" them as follows:
(but you will then lose the beginning of codes that start with one or more 0s)
and also check that during import, the data is not followed (this happens) by a special character or a space
in an empty cell: LEN(code cell) and see if the number of characters is indeed 7 or more
and if there is indeed an extra character, go back by uploading a template with a few examples of your codes on this site:
https://mon-partage.fr
and pasting here the link created on the site
thanks
thanks
--
The quality of the response mainly depends on the clarity of the question, thank you!
if you don't have a numeric filter in the menu, it’s probably because although your cells are in number format, the values remain as text.
See if this manipulation can help you:
- cell format / alignment /
- and horizontal >standard
if the codes display on the left, it means they are text
you can (maybe, but not sure) "digitize" them as follows:
- copy an empty cell
- select the column of codes
- right-click / special paste and check "add"
(but you will then lose the beginning of codes that start with one or more 0s)
and also check that during import, the data is not followed (this happens) by a special character or a space
in an empty cell: LEN(code cell) and see if the number of characters is indeed 7 or more
and if there is indeed an extra character, go back by uploading a template with a few examples of your codes on this site:
https://mon-partage.fr
and pasting here the link created on the site
thanks
thanks
--
The quality of the response mainly depends on the clarity of the question, thank you!
In my CSV, each piece of data in each cell is preceded by an apostrophe. If I remove this apostrophe, the number appears as it should (as a number) and my filter works.
So, I will insert a command in my macro to delete the first character of the cells and there you go, it's done.
Thank you for helping me progress on the path to Excellence!
Best regards,
safe travels
kind regards