custom format - extract text value for conditional processing

[margalo]

Hi - is there anyway to interrogate the value of text in a cell that is custom formatted? I have a spreadsheet where cells in a column are formatted as:
#,##0.00" USD";-#,##0.00" USD";#,##0.00;@
#,##0.00" PHP";-#,##0.00" PHP";#,##0.00;@
#,##0.00" MXN";-#,##0.00" MXN";#,##0.00;@

ETC.

I would like to do certain processing based on currency - but I only see the numeric value when I look at the cell. When i filter, I see the each numeric value with the currency - but how do extract the currency literal???

 

[MisterBates]

You'd need to use VBA to write a custom function that processed the .NumberFormat property

 

[pgc01]

Hi
Welcome to the board

You can also use a named formula to get the format.

For ex., to get the number format of the cell immediately on the left, define the name:

Name: GetFormat
Refers to: =GET.CELL(7,INDIRECT("RC[-1]",FALSE))

Now in B1:

=GetFormat

gives you the format in A1.

Remark: this formula will only update when some calculation is performed in the worksheet.

 

[Rick Rothstein]

Hi - is there anyway to interrogate the value of text in a cell that is custom formatted? I have a spreadsheet where cells in a column are formatted as:
#,##0.00" USD";-#,##0.00" USD";#,##0.00;@
#,##0.00" PHP";-#,##0.00" PHP";#,##0.00;@
#,##0.00" MXN";-#,##0.00" MXN";#,##0.00;@

ETC.

Let me understand your setup... you have a single column where the number formats as shown above are intermingled with each other down the column? Wow, that must have been a real pain entering your date if you had to custom format each cell individually! Have I understood your setup correctly? If so, did you use VB to perform these custom formats and, if so, would a VB solution be acceptable?