-2

How to get text instead of numbers by formatting? suppose a formula in cell gives numbers and I want that to be in text like

1 gives Excellent

2 gives Good

3 gives average

other number gives Bad thanks!

  • Are you talking about 1) changing the result in place from a number to text, 2) displaying a result in place as a text message instead of the result but keeping the result, or 3) displaying the text message in an adjacent cell based on the numerical result? – fixer1234 Apr 11 '15 at 0:11
3

You need a VLOOKUP function.

Create a second table/range of cells in your Excel sheet which has the number in one column and the text you want in the next column over. Then reference that "lookup" table in the function:

VLOOKUP( value, table, index_number, [not_exact_match] )

value is the value that you want to translate into the text, so you'll need to reference the cell that has the number. You can hide this column if you want.

See this link for more details and an example:

http://www.techonthenet.com/excel/formulas/vlookup.php

| improve this answer | |
1

This VBA code replaces the number for text and saves the original number on cell comments:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
Set wks = ActiveSheet
targetcolumn = Target.Column
If targetcolumn = 1 Then
    numericvalue = Target.Value
    Select Case numericvalue
        Case 1
            stringvalue = "Excellent"
            Target.ClearComments
            Target.AddComment Str(numericvalue)
            Target = stringvalue
        Case 2
            stringvalue = "Good"
            Target = stringvalue
            Target.ClearComments
            Target.AddComment Str(numericvalue)

        Case 3
            stringvalue = "Average"
            Target = stringvalue
            Target.ClearComments
            Target.AddComment Str(numericvalue)

        Case "Excellent", "Good", "Average", "Bad"
            fictionvalue = 1

        Case Else
            stringvalue = "Bad"
            Target = stringvalue
            Target.ClearComments
            Target.AddComment Str(numericvalue)

    End Select

End If
End Sub

It assumes that the column to check is column A.

To use it you have to open Macro, double-click the worksheet on the left bar and paste the code on the right side.

| improve this answer | |
1

Select the cells you wish to process and run this small macro:

Sub FakeFormat2()
    Dim DQ As String, mesage As String
    Dim r As Range
    DQ = Chr(34)
    For Each r In Selection
        v = r.Value
        mesage = DQ & "Bad" & DQ
        If v = 1 Then mesage = DQ & "Excellent" & DQ
        If v = 2 Then mesage = DQ & "Good" & DQ
        If v = 3 Then mesage = DQ & "Average" & DQ
        r.NumberFormat = mesage & ";" & mesage & ";" & mesage & ";"
    Next r
End Sub

The values in the cells will be unchanged, but the display formats will be the words you require.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.