Filter a currency column by searching with a number

[Synchestra]

Hello everyone,

I made an excel sheet that contains sales numbers in a column formatted for Accounting.
Looks like this: $ 1,300.00
Now when I search the column using the filter-function I only get results when I enter the number with a comma.
My boss would like to filter the column by just entering numbers without the comma. Is this possible?
To clarify:
I filter for 1300 -> no result
I filer for 1,300 -> I get $ 1,300.00 as a result.

Thanks in advance

Have a great day!

Synchestra

 

[forginganewone]

Hello everyone,

I made an excel sheet that contains sales numbers in a column formatted for Accounting.
Looks like this: $ 1,300.00
Now when I search the column using the filter-function I only get results when I enter the number with a comma.
My boss would like to filter the column by just entering numbers without the comma. Is this possible?
To clarify:
I filter for 1300 -> no result
I filer for 1,300 -> I get $ 1,300.00 as a result.

Thanks in advance

Have a great day!

Synchestra

I have an easy solution but it will take an amendment.
Lets say your accounting values column is A and another column next to it which will be B
=text($A1,"0")

This will convert the accounting figures into general setting and you can use auto filter on it by typing without comma

 

[Synchestra]

Hey thanks for the fast reply
I thought of something similar and I'm sure it works but since the table is already pretty crowded I would prefer a more elegant solution. If there is no other way I will use this suggestion thank you!

 

[forginganewone]

Hey thanks for the fast reply
I thought of something similar and I'm sure it works but since the table is already pretty crowded I would prefer a more elegant solution. If there is no other way I will use this suggestion thank you!

Press Ctrl+1 on the cell and use this custom format _([$$-409]* #,##0_);_([$$-409]* (#,##0);_([$$-409]* "-"_);_(@_)

by using this format it will keep the $ sign but remove the commas from values so that you can search with auto filter.