OK Khalil,
Is your problem solved now?
If not, post back what exactly remains to be solved
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Hi,
| The numbers in the sheet "tables" are actually the average of 2 or 3
nmbers
| of a different sheet.
| I used few experiments on formating the numbers in the sheet tables
based on
| the idea of having 16 digits and found out that it MIGHT WORK if all
numbers
| in the sheet "table" are ROUNDED with 0 digits and the format of the
cells
| is "general".
|
| | > The is as follows:
| > The number are actualy not in the English Language. That is why it
might
| > no loo reasonable the way it is arranged.
| > the number 97 with this translation should look like: seven and ninty
only
| > but it shows in the cell the value: five and ninty only
| > Hope this will help
| >
| >
| > '****************
| > ' Main Function *
| > '****************
| >
| > ' Converting numbers to words
| > ' Updated by Khalil Handal on 17/03/2007
| >
| >
| > Function GetTen(TensText)
| > Dim Result As String
| >
| > Result = "" ' Null out the temporary function
value.
| > If TensText = 100 Then
| > Result = "one Hundred"
| > GoTo one
| > Else
| > If Len(TensText) = 1 Then
| > Select Case Val(TensText)
| > Case 1: Result = "one mark" ' one mark
| > Case 2: Result = "Two marks" ' 2 marks
| > Case 3: Result = "three marks" ' 3 marks
| > Case 4: Result = "four marks" ' 4 marks
| > Case 5: Result = "Ifive marks" ' 5 marks
| > Case 6: Result = "Six marks" ' 6 marks
| > Case 7: Result = "Seven Marks " ' 7 marks
| > Case 8: Result = "Eight Marks" ' 8 marks
| > Case 9: Result = "Nine Marks" ' 9 marks
| >
| > Case Else
| > End Select
| > GoTo one
| > Else
| >
| > If Val(Left(TensText, 1)) = 1 Then ' If value between
10-19...
| > Select Case Val(TensText)
| > Case 10: Result = "Ten" ' 10 marks
| > Case 11: Result = "Eleven" ' 11 marks
| > Case 12: Result = "Twelve" ' 12 marks
| > Case 13: Result = "Thirteen" ' 13 marks
| > Case 14: Result = "Fourteen" ' 14 marks
| > Case 15: Result = "Fifteen" ' 15 marks
| > Case 16: Result = "Sixteen" ' 16 marks
| > Case 17: Result = "SevenTeen" ' 17 marks
| > Case 18: Result = "EEighteen" ' 18 marks
| > Case 19: Result = "Ninteen" ' 19 marks
| > Case Else
| > End Select
| > Else
| > ' If value between 20-99... first select the 20,30,40,50etc
| >
| > If Val(Right(TensText, 1)) = 0 Then
| > Select Case Val(Left(TensText, 1))
| > Case 2: Result = "Twenty " ' 20 marks
| > Case 3: Result = "Thirty " ' 30 marks
| > Case 4: Result = "Fourty " '40 marks
| > Case 5: Result = "Fifty " ' 50 marks
| > Case 6: Result = "Sixty " ' 60 marks
| > Case 7: Result = "Seventy " ' 70 marks
| > Case 8: Result = "Eighty " ' 80 marks
| > Case 9: Result = "Ninty " ' 90 marks
| > Case Else
| > End Select
| > GoTo one
| > 'then select any other value between 21 and 99
| > Else
| > Select Case Val(Left(TensText, 1))
| > Case 2: Result = "Twenty only " ' 20 marks
| > Case 3: Result = "Thirty only " ' 30 marks
| > Case 4: Result = "Fourty only " ' 40 marks
| > Case 5: Result = "Fifty only " ' 50 marks
| > Case 6: Result = "Sixty only " ' 60 marks
| > Case 7: Result = "seventy only " ' 70 marks
| > Case 8: Result = "Eighty only " ' 80 marks
| > Case 9: Result = "Ninty only " ' 90 marks
| > Case Else
| > End Select
| > Result = GetDigi(Right(TensText, 1)) & " and" & Result '
| > Retrieve ones place.
| >
| > End If
| > End If
| > End If
| > End If
| > one: GetTen = Result
| > End Function
| >
| >
| >
| >
| > '*******************************************
| > ' Converts a number from 1 to 9 into text. *
| > '*******************************************
| >
| > Function GetDigi(Digit)
| > Select Case Val(Digit)
| > Case 1: GetDigi = "one " ' Ihda
| > Case 2: GetDigi = "two " ' Ithnatan
| > Case 3: GetDigi = "three " ' thalath
| > Case 4: GetDigi = "four " ' Arba
| > Case 5: GetDigi = "five " ' Khamsu
| > Case 6: GetDigi = "six " ' Sittu
| > Case 7: GetDigi = "seven " ' Sabau
| > Case 8: GetDigi = "eight " ' Thamani
| > Case 9: GetDigi = "nine " ' Tesau
| > Case Else: GetDigi = "" ' empty
| > End Select
| > End Function
| >
| >
| >
| >
| > | >> Both Dane and Bernard asked for the code of the function. So do I.
| >>
| >> What if you format A3 to 6 or even more digits? Try 16 digits.
| >> Format the table (B14:B93) to 16 digits as well.
| >>
| >> --
| >> Kind regards,
| >>
| >> Niek Otten
| >> Microsoft MVP - Excel
| >>
| >>
| >>
| >> | >> | 1- It is not related to the vlookup.
| >> | 2- copying the cell to another place with the sugested format
(number
| >> with 6
| >> | digits) still give the same wrong interpretation.
| >> | 3- the GetTen function is used as follows:
| >> | in cel g23 the value is 98 , in cell H23 it should write the
value
| >> in
| >> | words. i.e. ninety eight.
| >> |
| >> |
| >> |
| >> | | >> | > From what you say the value in G23 always looks correct but
GetTen
| >> | > sometimes fails to give the right words. Correct?
| >> | > Maybe the number is G23 is formatted to show something different
from
| >> what
| >> | > is stored.
| >> | > In an empty cell enter =G23 and format it as number with 6
decimal
| >> places
| >> | > Does it look different from G23?
| >> | > Also tell us what the code is for GetTen
| >> | > best wishes
| >> | > --
| >> | > Bernard V Liengme
| >> | > Microsoft Excel MVP
| >> | >
http://people.stfx.ca/bliengme
| >> | > remove caps from email
| >> | >
| >> | > | >> | >> Hi,
| >> | >> Cell G23 in sheet "Cert_End" has the formula:
| >> | >>
| >>
=IF(VLOOKUP($A$3,Table!$B$14:$BD$93,ROW(A4)*3+3,FALSE)<>"",(VLOOKUP($A$3,Table!$B$14:$BD$93,ROW(A4)*3+3,FALSE)),"")
| >> | >> It will look up the number from sheet "table" that corresponds
with
| >> the
| >> | >> value in cell A3 in sheet .
| >> | >> At the cell H23, I have the formula: =GetTen(G23) where GetTen
is a
| >> | >> function that converts number into words: (98 in cell G23 will
have
| >> | >> Ninety Eight in cell H23).
| >> | >>
| >> | >> My problem is that I see the value "Ninty five" instead of
"ninty
| >> eight".
| >> | >> For some cells it works fine but for other cells it does not
work.
| >> | >> If I type the value manually it works without any mistakes.
| >> | >> Any Ideas!!!!!
| >> | >>
| >> | >>
| >> | >
| >> | >
| >> |
| >> |
| >>
| >>
| >
| >
|
|
