Numberformat that allows me to enter formulas but stores values as text?

499 views Asked by At

Is it possible to set the numberformat in a cell/column using either Excel or VBA so that:

  • if I enter a formula (anything starting with =) Excel will calculate the formula (and not interpret it as just text)
  • if I enter a value, for example 5.80, Excel will store it as text?

I'm having a problem where I want all user input to be stored as text, but users should also be able to enter formulas. If I set the numberformat to text, formulas aren't interpreted. If I set the numberformat to general, values are stored as numbers.

4

There are 4 answers

4
Siddharth Rout On BEST ANSWER

Here is my version.

Format all cells in that sheet as Text. This code uses Application.Evaluate() to evaluate all formulas and store the result as text.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range

    On Error GoTo Whoa

    Application.EnableEvents = False

    '~~> You need this in case user copies formula
    '~~> from another sheet
    Target.Cells.NumberFormat = "@"

    '~~> Looping though all the cells in case user
    '~~> copies formula from another sheet
    For Each aCell In Target.Cells
        If Left(aCell.Formula, 1) = "=" Then _
        aCell.Value = Application.Evaluate(aCell.Formula)
    Next

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub
0
Ronnie Royston On

Conditional Formatting

Highlight range you want affected and click conditional formatting. Apply as shown below. You want to format cells not containing text "=" to "text".

enter image description here

0
Comintern On

You can force Excel to interpret a number as a string by appending a single quote to the start of the number even if the cell format is set to General:

ActiveSheet.Cells(1, 1).Value = "'5.80"
'...or...
ActiveSheet.Cells(2, 1).Value = "'" & Format$(58 / 10, "#.00")
3
Gary's Student On

Easy...........pre-format the cell to Text, then have an Event macro monitor the cell and change the format to General if a formula is entered; and then force formula execution. For example cell B9:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B9 As Range
    Set B9 = Range("B9")
    If Intersect(Target, B9) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    With B9
        If Left(.Value, 1) = "=" Then
            .NumberFormat = "General"
            .Value = .Value
        Else
            .NumberFormat = "@"
        End If
    End With
    Application.EnableEvents = True
End Sub