filter on currency number format

[FMS]

Hi

I fear I may be overly optimistic here, but it's worth a shot.

I've got a column in a spreadsheet that has a mix of currencies with the
display showing the currency letter from Number Formatting eg
USD 2200
EUR 3000
KES 5500
USD 2500

I'd love to be able to filter and show only the USD items but, even though
the currency letters show in the custom filter drop downs, it's not showing
anything when I try begins with USD.

I need to perform calcs from these items and hence can't use text and I also
can't use separate columns for each currency.

Is there anyway to filter on number formatting?

many thanks
FMS

 

[FMS]

visible only as a result of the formatting

Is the U in USD actually in the cell, or is it visible only as a result of
formatting??

 

[Gary''s Student]

First insert this tiny User Defined Function in a standard module:

Function txet(r As Range) As String
txet = r.Text
End Function

Then if your data is in column A, insert in B1

=txet(A1) and copy down

We see something like:

value value
USD 2200 USD 2200
EUR 3000 EUR 3000
KES 5500 KES 5500
USD 2500 USD 2500

Column B LOOKS like column A, but the letters are now real.

You can filter on column B
--------------------------------------------------------

UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

 

[FMS]

Wow, that's fantastic!

I won't even pretend to understand how or why it works, but it does and I'm
extremely grateful.

thank you so much