Is there a way to use the formatted output from a cell, rather than the unformatted value?

user_13606268 0
Viewed 66

Is there a way to generically grab the text output of a numeric formatted cell? For example:

(A) I have a cell formatted as Percent - the cell may well show "20%" while containing the value 0.2 (B) I have another cell formatted as Currency, showing "$20,000" where the actual value is 20000

I want to be able to extract (generically) the text result of these cells without knowing ahead of time what the format in use is in the particular cell. So imagine a function

=CellText(A1)

  • If I point it at (A) I would get "20%" as the result
  • if I point it at (B) I would get "$20,000" as the result

Is there anything already like this in Excel? Ideally I'd like to accomplish it without VBA ...

Thanks, Ken

excel-formula vba excel
2 Answers

In VBA, to get the formatted content of some cell use

SomeCell.Text

where SomeCell is a range object set to a single cell.

user_4454250

From what I can see, you would need VBA as stated. For example:

Sub TestFormat()
    MsgBox GetCellFormatValue(ActiveCell)
End Sub

Function GetCellFormatValue(r As Range) As Variant
    If r.Cells.Count > 1 Then Exit Function
    On Error Resume Next
    GetCellFormatValue = Format(r.Value, r.NumberFormat)
End Function
user_36888610
Related