Use formulas on rendered/display value of an excel cell

1
votes

I have a column in Excel with Currency values. They are formatted to 2 decimal places and have a currency code prefix (using the "Format Cells" option).

For example, the raw value:

10000

gets displayed as:

ZMW 10,000.00

Please note this is done using formatting, not a formula.

Now I want to use the display value of this cell (i.e. ZMW 10,000.00) in a formula in another cell (e.g. =LEFT($A$1, 3)). However the formula always works on the raw value of the cell, not its display value.

I tried multiple options, but wasn't able to get a formula working in excel on display value of a cell. Can someone please advice how this can be achieved.

1
excelexcel-formula

1 Answers

1
votes

The easiest method of retrieving the displayed text (not the raw value) from any formatted cell is the Range.Text property.

Module1 code sheet

Option Explicit

Function cellText(rng As Range)

    cellText = rng.Text

End Function

In the following, A1 has a custom number format of Z\MW #,##0.00 and a raw value of 10000.

=LEFT(cellText(A1), 3)

enter image description here