number to word

annadinesh

New Member
Joined
Mar 1, 2017
Messages
22
HI

is it possible to see the number/amount in words if we hover mouse cursor on a number in excel as a comment

not in any cell, it will show as a comment box and when we move the cursor from the number the comment should vanish


thanks


dinesh saha
9932022569
 
Last edited:

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
Assuming that in A2 you have the amount. In cell B2 put the formula:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">127</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IFERROR(HYPERLINK(NewEvento(A2)),"")</td></tr></table></td></tr></table>

Put the following code in a module:

Code:
Public Function NewEvento(rngCelda As Range)
  CreateObject("WScript.Shell").PopUp SpellNumber(rngCelda.Value), 1, "Amount", 64
End Function


Function SpellNumber(ByVal MyNumber)
  Dim Dollars, Cents, Temp
  Dim DecimalPlace, Count
  ReDim Place(9) As String
  Place(2) = " Thousand "
  Place(3) = " Million "
  Place(4) = " Billion "
  Place(5) = " Trillion "
  ' String representation of amount.
  MyNumber = Trim(Str(MyNumber))
  ' Position of decimal place 0 if none.
  DecimalPlace = InStr(MyNumber, ".")
  ' Convert cents and set MyNumber to dollar amount.
  If DecimalPlace > 0 Then
  Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
  MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
  End If
  Count = 1
  Do While MyNumber <> ""
  Temp = GetHundreds(Right(MyNumber, 3))
  If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
  If Len(MyNumber) > 3 Then
  MyNumber = Left(MyNumber, Len(MyNumber) - 3)
  Else
  MyNumber = ""
  End If
  Count = Count + 1
  Loop
  Select Case Dollars
  Case ""
  Dollars = "No Dollars"
  Case "One"
  Dollars = "One Dollar"
  Case Else
  Dollars = Dollars & " Dollars"
  End Select
  Select Case Cents
  Case ""
  Cents = " and No Cents"
  Case "One"
  Cents = " and One Cent"
  Case Else
  Cents = " and " & Cents & " Cents"
  End Select
  SpellNumber = Dollars & Cents
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
  Dim Result As String
  If Val(MyNumber) = 0 Then Exit Function
  MyNumber = Right("000" & MyNumber, 3)
  ' Convert the hundreds place.
  If Mid(MyNumber, 1, 1) <> "0" Then
  Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
  End If
  ' Convert the tens and ones place.
  If Mid(MyNumber, 2, 1) <> "0" Then
  Result = Result & GetTens(Mid(MyNumber, 2))
  Else
  Result = Result & GetDigit(Mid(MyNumber, 3))
  End If
  GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
  Dim Result As String
  Result = "" ' Null out the temporary function value.
  If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
  Select Case Val(TensText)
  Case 10: Result = "Ten"
  Case 11: Result = "Eleven"
  Case 12: Result = "Twelve"
  Case 13: Result = "Thirteen"
  Case 14: Result = "Fourteen"
  Case 15: Result = "Fifteen"
  Case 16: Result = "Sixteen"
  Case 17: Result = "Seventeen"
  Case 18: Result = "Eighteen"
  Case 19: Result = "Nineteen"
  Case Else
  End Select
  Else ' If value between 20-99...
  Select Case Val(Left(TensText, 1))
  Case 2: Result = "Twenty "
  Case 3: Result = "Thirty "
  Case 4: Result = "Forty "
  Case 5: Result = "Fifty "
  Case 6: Result = "Sixty "
  Case 7: Result = "Seventy "
  Case 8: Result = "Eighty "
  Case 9: Result = "Ninety "
  Case Else
  End Select
  Result = Result & GetDigit _
  (Right(TensText, 1)) ' Retrieve ones place.
  End If
  GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
  Select Case Val(Digit)
  Case 1: GetDigit = "One"
  Case 2: GetDigit = "Two"
  Case 3: GetDigit = "Three"
  Case 4: GetDigit = "Four"
  Case 5: GetDigit = "Five"
  Case 6: GetDigit = "Six"
  Case 7: GetDigit = "Seven"
  Case 8: GetDigit = "Eight"
  Case 9: GetDigit = "Nine"
  Case Else: GetDigit = ""
  End Select
End Function

When you mouse over cell B2, the message will appear with the amount in letters
 

annadinesh

New Member
Joined
Mar 1, 2017
Messages
22
Thanks for the reply,

But i want the comment should show on the same cell where the number is
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,192
Office Version
2007
Platform
Windows
I didn't find much about the same cell, you'll have to investigate something about "tooltips on cell"
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,290
Try putting Dante's function in a standard module, then put this on the VBA sheet page corresponding to the sheet where you want the comments:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Target.AddComment
    Target.Comment.Visible = False
    Target.Comment.Text Text:=SpellNumber(Target.Value)
End Sub
This will add a comment to any cell you select and display the amount. It won't remove the comment when you select another cell, although we could probably figure something out. You could just remove the comment indicators:

Code:
    Application.DisplayCommentIndicator = xlNoIndicator
 

Forum statistics

Threads
1,089,373
Messages
5,407,882
Members
403,167
Latest member
LakesideChill19

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top