Put a number on a cell and it showing word/words

Tracyng2406

New Member
Joined
Feb 26, 2018
Messages
9
Hi All,

Could anyone teach me how to put a number (e.g. 1) in a cell, after enter it will show a word (e.g. apple / sweet apple) ?

thanks a lot.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Welcome to the MrExcel board!

One way is to construct a lookup table (I used columns D:E) and then use a VLOOKUP formula. An example below where the formula in cell B2 is copied down.


Book1
ABCDE
11Apple1Apple
23Banana2Pear
32Pear3Banana
41Apple
Lookup
Cell Formulas
RangeFormula
B1=VLOOKUP(A1,$D$1:$E$3,2,0)



If you want to actually replace the 1 with "Apple" in the same cell then you will need a macro solution. If that is what you want, post back.
 
Last edited:
Upvote 0
Hi Peter,

thanks a lot for your reply.

Yes, actually I want to replace the 1 with "Apple in the same cell, is it only way need a macro solution? would it be complicated?
 
Upvote 0
Yes, actually I want to replace the 1 with "Apple in the same cell, is it only way need a macro solution? would it be complicated?
Yes, it will need to be done by vba.
1. How will we know what words to replace what numbers? Will there be a list somewhere in your workbook?
2. How big will the list of replacements be?
 
Upvote 0
Thanks again Peter,

I just made an example as below picture. I've made a survey from my colleagues and now I need to it into excel format. but my real project have long words, so I would like to input like 1,2 .... A,B,C.....I,II and it can be show apple/ orange....Red/Blue......Open/Close automatically. is it possible? thanks a lot.


[TABLE="width: 550"]
<colgroup><col><col span="7"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"][/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]Open[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"][/TD]
[TD="align: center"]II[/TD]
[TD="align: center"]Close[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]Purple[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Yellow[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]Green[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Fruit[/TD]
[TD="align: center"]Colour[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Ann[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Susan[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]Robert[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Jade[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Sue[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]Helen[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]Jeffery[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]Joey[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]Dicky[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]Suki[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]Jimmy[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Good Day
First Alt+F11 >> go to VBAproject >>> Cleck ThisWorkBook >> Copy Below Code and Past
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim MyNum As Long
 If Target.Value <> "" And IsNumeric(Target.Value) = True Then
 MyNum = 60 - WorksheetFunction.Ceiling(Target.Value, 60) + Target.Value
 Target = GeEnumValues("StrEnumVal", MyNum)
 End If

End Sub


BSv3r2m.png


Then insert New Standard Module Copy Below Code and Past
Code:
Public Enum fruit_and_veg

        Apple = 1
        Apricot = 2
        Asparagus = 3
        Aubergine = 4
        Avocado = 5
        Banana = 6
        Beetroot = 7
        Black_eye_bean = 8
        Broad_bean = 9
        Broccoli = 10
        Brussels_sprout = 11
        Butternut_Squash = 12
        Carrot = 13
        Cherry = 14
        Clementine = 15
        Courgette = 16
        Date = 17
        Elderberry = 18
        Fennel = 19
        Fig = 20
        Garlic = 21
        Grape = 22
        Green_bean = 23
        Guava = 24
        Haricot_bean = 25
        Honeydew_melon = 26
        Iceberg_lettuce = 27
        Jerusalem_artichoke = 28
        Kiwi_fruit = 29
        Leek = 30
        Lemon = 31
        Mango = 32
        Melon = 33
        Mushroom = 34
        Nectarine = 35
        Nut = 36
        Olive = 37
        Orange = 38
        Pea = 39
        Peanut = 40
        Pear = 41
        Pepper = 42
        Pineapple = 43
        Pumpkin = 44
        Quince = 45
        Radish = 46
        Raisin = 47
        Rhubarb = 48
        Satsuma = 49
        Strawberry = 50
        Sweet_potato = 51
        Tomato = 52
        Turnip = 53
        Ugli_fruit = 54
        Victoria_plum = 55
        Vine_leaf = 56
        Watercress = 57
        Watermelon = 58
        Yam = 59
        Zucchini = 60

End Enum

Function GeEnumValues(PrcName As String, EnumItm As Long)

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Reference:Microsoft Visual Basic for Extensibility 5.3 is required'
    '     Fahad Mubark AL-Dossary                                      '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim ProcStrLn As Long, ProcAcStrLn As Long, ProcCntLn As Long, N As Long, D As Long, S As Long, PrcCnountLine As Long
    Dim DecStrLn As Long, DecEndLn As Long
    Dim ThisLine As String, Dec As String, ThisSub As String, Itm As String
    Dim DecItm As Variant
    Set VBProj = ThisWorkbook.VBProject
        For Each VBComp In VBProj.VBComponents
            With VBComp
            If .Type = vbext_ct_StdModule Then ' Withen Standr Module
            With .CodeModule
            If InStr(1, .Lines(1, .CountOfLines), PrcName) > 0 Then 'Replace Sub Function
                On Error Resume Next
                ProcStrLn = .ProcStartLine(PrcName, vbext_pk_Proc) ' Procedure Start Line
                ProcAcStrLn = .ProcBodyLine(PrcName, vbext_pk_Proc) ' Actually Procedure Start Line
                ProcCntLn = .ProcCountLines(PrcName, vbext_pk_Proc)
                PrcCnountLine = ProcCntLn - (ProcAcStrLn - ProcStrLn)
                If ProcAcStrLn > 0 Then
                Else '____________________________________________________________________________________________________
                    ' Replce Declaration such as Enum
                    For D = 1 To .CountOfDeclarationLines
                        ThisLine = .Lines(D, 1)
                        If InStr(1, ThisLine, "Enum " & PrcName) > 0 Then
                            Titl = DecItm(D)
                            Dec = Dec & vbNewLine & ThisLine: DecStrLn = D
                            S = InStr(1, ThisLine, "Enum " & PrcName) + Len("Enum " & PrcName) 'Start replace column
                        ElseIf InStr(1, Dec, "Enum " & PrcName) > 0 And InStr(1, ThisLine, "End Enum") > 0 Then
                            Dec = Dec & vbNewLine & ThisLine: DecEndLn = D
                            Exit For
                        ElseIf InStr(1, Dec, "Enum " & PrcName) Then
                            Dec = Dec & vbNewLine & ThisLine
                        End If
                    Next 'Declaration
                    ' MsgBox .Lines(DecStrLn, DecEndLn - DecStrLn + 1) '=MsgBox Dec 'Declaration
                End If '_______________________________________________________________________________________________________
                    On Error GoTo 0
                        End If
                    End With ' .CodeModule
                        End If ' .Type
                    End With ' VBComp
        Next ' In VBProj.VBComponents
        'Declaration
        DecItm = Split(Dec, vbNewLine)
            For D = LBound(DecItm) To UBound(DecItm)
                      Itm = DecItm(D)
                      If Itm <> "" And InStr(1, Itm, "Enum " & PrcName, vbTextCompare) = 0 And InStr(1, Itm, "End Enum") = 0 Then
                        If InStr(1, Itm, " = ", vbTextCompare) > 0 Then
                            N = Split(Itm, " = ")(1)
                        Else
                            Itm = Itm & " = " & N
                        End If
                        If EnumItm = N Then
                          GeEnumValues = Trim(Split(Itm, " = ")(0))
                          Exit Function
                        End If
                        N = N + 1
                      End If
            Next
  
End Function
go to VBAProject Tools >>> Reference and scroll done till "Microsoft Visual Basic for Extensibility 5.3 " this Reference is required
or run below Code
Code:
[COLOR=#008000]Public Sub Add_Microsoft_Visual_Basic_for_Extensibility()[/COLOR]
[COLOR=#008000]    AddRef ThisWorkbook, "{0002E157-0000-0000-C000-000000000046}", "VBIDE", 5, 3 'if need or delete this line. To select required Reference[/COLOR]
[COLOR=#008000]End Sub[/COLOR]
Sub AddRef(wbk As Workbook, sGuid As String, sRefName As String, sRefMajor As Long, sRefMinor As Long)
        Dim i As Integer
        On Error GoTo EH
        With wbk.VBProject.References
        For i = 1 To .Count
        If .Item(i).Name = sRefName Then
            Exit For
        End If
            Next i
        If i > .Count Then
    
       .AddFromGuid sGuid, sRefMajor, sRefMinor ' 0,0 should pick the latest version installed on the computer
    End If
        End With
EX:     Exit Sub
EH:     MsgBox "Error in 'AddRef'" & vbCrLf & vbCrLf & Err.Description
        Resume EX
        Resume ' debug code
        ThisWorkbook.Save
End Sub
 
Last edited:
Upvote 0
correction
I m sorry I have mistake
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Dim MyNum As Long
 If Target.Value <> "" And IsNumeric(Target.Value) = True Then
 MyNum = 60 - WorksheetFunction.Ceiling(Target.Value, 60) + Target.Value
'[COLOR=#333333]Target = GeEnumValues([/COLOR][B][COLOR=#ff0000]"StrEnumVal"[/COLOR][/B][COLOR=#333333], MyNum)' [/COLOR][COLOR=#ff0000]X  wrong[/COLOR]
[COLOR=#006400] Target = GeEnumValues([/COLOR][B][COLOR=#008000]"fruit_and_veg[/COLOR][/B][COLOR=#008000]"[/COLOR][COLOR=#006400], MyNum)' / Right[/COLOR]
 End If


End Sub

Feed Me Back PLS
 
Last edited:
Upvote 0
Thanks for Dossfm0q details reply, but I am sorry that it's too complicated and I just need basic excel formula.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top