Filtering Text/numbers

Topic

New Reply

A column that is a numeric/alphabetic fields. Formatted as text will not filter. It comes up blank. But if I select one of the exact numbers it works, but not with the Begin as or using wild characters. I tried the solution that was in the Microsoft Knowledge base (Article ID: Q170230) but it still doesn’t work. Any suggestions? Attached is a sample of the file. Try to filter column “E” for Items beginning with 299.

Reply | Quote

Replies

My understanding is that you can’t do a “begins with” filter on numbers, as “begins with” is a string comparison. If you want to filter on all the numbers starting with 299, then I would suggest filtering on “>=290000 and <300000".

Jon

Reply | Quote

That would be fine if it was all numeric but it some examples of the field is: 12345, 1298TC. I want everything that starts with 12. The numeric will not work in the TC instance. It doesn’t pull them.

Reply | Quote

Sorry, I didn’t realise you were mixing your data. In that case, my only other suggestions are:
1) along the lines of article q170230, except that I would have created the new data with the space at the end of the cell, rather than the beginning.
2) create a new column and use the Text function with the number format “General” to convert everythin to strings.

See how you go with those and let me know.

Jon

Reply | Quote

Thanks for your help. I figured it out. Insert column, format column to GENERAL then put in the following formula in 1st cell of new column: =IF(T(##)=””,FIXED(##,0,TRUE),##) where the ## is the cell reference of number need converted. Then drag down formula to last row, copy, paste special, values. Delete old column.

For more details I wrote it up in the attached file.

Reply | Quote