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!
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityHow 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!
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:
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.
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.