Excel Function – DisplayValue – Copy Display Value | Format Cell

If you want to copy Display value in Excel which different from the actual value in Excel. You are at right place.

Most of the time with format cell option we can change display of actual value, but there is no option in excel to copy the same value to another cell as original value.

You cannot apply MID, LEFT or RIGHT function on that cell.

Solution for the same is here.

Just copy Below code in VBA Module (Alt+F11) and you will get new function.

=DisplayValue(#REF).

Public Function DisplayValue(ByVal a As Range) As String
  DisplayValue = a.Text
End Function

Just copy output of above function and make PasteSpecial [Alt+Ctrl+V] >> Value. You will get display output as Text in distination cell.

Let’s use it and Enjoy!