top bar
QuickTopic free message boards logo
Skip to Messages

TOPIC:

Help with Excel functions (www.meadinkent.co.uk)

^               2547-2562 of 2562  2531-2546 >>
2562
Smileyface
06-02-2017
09:57 PM BST
Hi Chris,

Is it easy to "switch" your WordNum Function, so that if the user supplies a value in text, eg one thousand and fifty, it returns the value 1050?

Thanks

captainmarkov@hotmail.com
2561
Iain Whyte
16-01-2017
04:41 PM BST
I used to be able to do names and addresses on Excel. I want to find out how I

a) Close the gaps on the chart with deleted addresses
b) Re arrange the surnames alphabetically (I have already inputted several new names and addresses

Very grateful for tips - I cant navigate my way round the Filters, Sorts as I remember before.
2560
John
23-11-2016
04:10 PM BST
Hi Everyone,

Here is an Excel number speller (using some www.meadinkent.co.uk VBA, Visual Basic for Applications, code I tweaked) I wrote that either displays any decimal number entered as a decimal number rounded to thousandths or dollars and cents. I would love to have it read decimals all the way to millionths, billionths, trillionths, etc., but VBA tends to stump me. Lol Any help would be appreciated. My email is treeguywussup-meadinkent@yahoo.com

The sheet uses range B1:C5, cell B1 was named "CurrencySymbol" and limited to a list of "" (i.e., a blank cell or "empty string") or "$." Here are example results followed by formulae and VBA:

Sample Results (with CurrencySymbol = "empty string," ie., "")
1,234,567.891501
1,234,567.891501
one million, two hundred thirty-four thousand, five hundred sixty-seven
eight hundred ninety-two thousandths
one million, two hundred thirty-four thousand, five hundred sixty-seven and eight hundred ninety-two thousandths

Sample Results (i.e., with CurrencySymbol = "$"):
$ 1,234,567.891501
 $1,234,567.89
 one million, two hundred thirty-four thousand, five hundred sixty-seven dollars
 eighty-nine cents
 one million, two hundred thirty-four thousand, five hundred sixty-seven dollars and eighty-nine cents

Formulae (i.e., with cell B1 or CurrencySymbol value = "$"):
$ 1234567.891501
 =IF(CurrencySymbol="$",TEXT(C1,"$#,##0.00"),C1)
 =wordnum(C1)&IF(CurrencySymbol<>"$",""," dollars")
 "=IF(CurrencySymbol=""$"",wordnum(ROUND((C1-INT(C1)),2)*100)&"" cents"",
wordnum(ROUND(($C$1-INT($C$1)),3)*1000)&"" thousandths"")"
 =C3&" and "&C4

VBA - Visual Basic for Applications code, i.e., press Alt+F11! ;-)
Option Explicit
Public Numbers As Variant, Tens As Variant

Sub SetNums()
Numbers = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen")
Tens = Array("", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety")
End Sub

Function WordNum(MyNumber As Double) As String
Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
' This macro was written by Chris Mead - www.MeadInKent.co.uk

If Abs(MyNumber) > 999999999 Then
 WordNum = "Value too large"
 Exit Function
End If

SetNums

' String representation of amount (excl decimals)
NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
' NumStr = Right("000000000000" & Trim(Str(Int(Abs(MyNumber)))), 12) '[TRIED MODIFYING 23Nov2016]
ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))

For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
StrNo = Format(ValNo(n), "000")
' StrNo = Format(ValNo(n), "000000") '[TRIED MODIFYING 23Nov2016]

If ValNo(n) > 0 Then
Temp1 = GetTens(Val(Right(StrNo, 2)))
If Left(StrNo, 1) <> "0" Then
 Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
' If Temp1 <> "" Then Temp2 = Temp2 & " and "
 If Temp1 <> "" Then Temp2 = Temp2 & " "
Else
 Temp2 = ""
End If

If n = 3 Then
' If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
' If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = ", "
 If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = " "
 WordNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand, " & WordNum)
If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million, " & WordNum)

End If
Next n

NumStr = Trim(Str(Abs(MyNumber)))

' Values after the decimal place
DecimalPosition = InStr(NumStr, ".")
Numbers(0) = "zero"
If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
 Temp1 = " point"
 For n = DecimalPosition + 1 To Len(NumStr)
  Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
 Next n
' WordNum = WordNum & Temp1
 WordNum = WordNum
End If

If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
 WordNum = "zero" & WordNum
End If
End Function

Function GetTens(TensNum As Integer) As String
' Converts a number from 0 to 99 into text.
If TensNum <= 19 Then
 GetTens = Numbers(TensNum)
Else
 Dim MyNo As String
 MyNo = Format(TensNum, "00")
' GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
 GetTens = Tens(Val(Left(MyNo, 1))) & "-" & Numbers(Val(Right(MyNo, 1)))
End If
End Function
  Messages 2559-2557 deleted by author between 11-23-2016 11:08 AM and 11-23-2016 10:55 AM
2556
Yomesh Triapthi
23-06-2016
01:33 PM BST
Hi all

for Number to text i got one script for excel add-in n this place i got from there. i have small issue with it. It's working fine now i was using this to print cheque working fine but 1, it's writing in only one line so if amount are bigger then it change the font size and after the amount in alphabet i need this word and of the line "Only." can you help me for this i am not programmer so i not able to do this part.
Option Explicit

Public Numbers As Variant, Tens As Variant

Sub SetNums()
    Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub

Function WordNum(MyNumber As Double) As String
    Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
    Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
    ' This macro was written by Chris Mead - www.MeadInKent.co.uk
    If Abs(MyNumber) > 999999999 Then
        WordNum = "Value too large"
        Exit Function
    End If
    SetNums
    ' String representation of amount (excl decimals)
    NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
    ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))
    For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
        StrNo = Format(ValNo(n), "000")
        If ValNo(n) > 0 Then
            Temp1 = GetTens(Val(Right(StrNo, 2)))
            If Left(StrNo, 1) <> "0" Then
                Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
                If Temp1 <> "" Then Temp2 = Temp2 & " and "
            Else
                Temp2 = ""
            End If
            If n = 3 Then
                If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
                WordNum = Trim(Temp2 & Temp1)
            End If
            If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
            If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)
        End If
    Next n
    NumStr = Trim(Str(Abs(MyNumber)))
    ' Values after the decimal place
    DecimalPosition = InStr(NumStr, ".")
    Numbers(0) = "Zero"
    If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
        Temp1 = " point"
        For n = DecimalPosition + 1 To Len(NumStr)
            Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
        Next n
        WordNum = WordNum & Temp1
    End If
    If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
        WordNum = "Zero" & WordNum
    End If
End Function

Function GetTens(TensNum As Integer) As String
' Converts a number from 0 to 99 into text.
    If TensNum <= 19 Then
        GetTens = Numbers(TensNum)
    Else
        Dim MyNo As String
        MyNo = Format(TensNum, "00")
        GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
    End If
End Function




Thank's for what i have all ready from this script.
Yomeshtripathi@gmail.com
Edited 23-06-2016 01:35 PM
2555
Robert Dahlke
18-05-2016
09:31 PM BST
Hi all,

I'm trying to create a student loan calculator in Excel(google sheets) where I can add additional payments but it would only add it for a specific date not every single payment. I've used the formula: =if(day(A27)=Day($B$5),$B$6,"") where A27 is date, B5 is my scheduled monthly payment date, B6 is payment amount. This works for changing my scheduled monthly payment to a specified day of the month throughout the sheet, but what I want to do is allow 20 rows for additional payment dates,

For example B10:B30 (any date for additional payment) C10:C30 (amount of additional payment). A31:A1000 (date in 1 day increments, starts at 1/1/2016, 1/2/2016, etc) D31:D1000 (Additional payment amount). I want to enter 1/2/2016 into B10, $30 into C10 and then $30 is pops into D32. Then enter 1/5/2016 into B11, $40 into C11, then $40 pops up into D35.

Any thoughts? Any help would be greatly appreciated.

Best,

Robert
rpdahlke@gmail.com
2554
William
15-04-2016
06:22 PM BST
This is the first time I have used the forum/site. I found it today while search for a database to save all my business contacts. I found the Associate leave planner and thought that it would be a great tool if the titles were editable. Does anyone know if the leave title could be changed to QAV? I would like to use the tool for my Supplier travel Visits if possible. Thanks for the help
2553
Kevin
13-07-2015
01:43 PM BST
Hi there,
I have tried using the numbers to text with some success however I wish to use it for UK currency. The 'point' whatever value needs to be in pence and whole numbers i.e. twenty not two, zero.

I have tried a formula to use the WordNum which does actually work quite well but falls over sometimes, for example 11 pence would be 11 pence but 10 pence would be one pence and the zero ignored. The formula is:
=CONCATENATE(wordnum(ROUND(L22,0))," POUNDS ",wordnum(RIGHT(L22,2))," PENCE")

Anyway I hope this can be solved!

Many thanks
Kevin
kjthorley@gmail.com
2552
PUSHKAR P BHATKHANDE
09-07-2015
11:24 AM BST
The number to word conversion code (wordnum) doesn't contai the term "LAC" or "LACS" as well as "Crore" or "Crores". let me please know which changes need to be done in the code for INDIAN CURRENCY STYLE. MY E-Mail Address is p.bhatkhande@pcmcindia.gov.in or pushkarbh@hotmail.com. please revert back as early as possible.
2551
Rajiv
22-05-2015
05:02 AM BST
Hi, Our Team using Shared Excel file, file contain 1000 data in a row + column (7 to 8 users)
few user are using MS Excel 2003 & few are using MS Excel 2007, Many times it's happened file getting locked by some user.
Kindly confirm how to avoid such error & why such error is happened in sharing file.
Kindly suggest best possible solution & Thanks in advance...!!
My email id is Rajiv.rank1@tata-aig.com & rajiv.rank85@gmail.com
2550
Jakeneedshelp
21-05-2015
03:51 PM BST
So I'm using excel and I have a question. so I am building a bid worksheet. in sheet1 I have a bunch of crap. in sheet2 I have a bunch of Item names in A2:A120 then In B2:B120 I have the Price of said items. so I was wondering if there is a way or formula so lets say in a cell I type in the word "Honeywell" then in another cell next to it, it prints the price "$$$" any help or guidance would be helpful.
2549
Rajiv
13-03-2015
05:04 AM BST
Hi, Our Team using Shared Excel file, file contain 1000 data in a row + column (7 to 8 users)
few user are using MS Excel 2003 & few are using MS Excel 2007, Many times it's happened file getting locked by some user.
Kindly confirm how to avoid such error & why such error is happened in sharing file.
Kindly suggest best possible solution & Thanks in advance...!!
My email id is Rajiv.rank1@tata-aig.com & rajiv.rank85@gmail.com
2548
kerry
12-03-2015
01:39 PM BST
I want to provide the option "other" in drop down lists AND if "other" is selected I want a way of allowing space to specify what that "other" is, Is this possible? very many thanks in advance
2547
Rajiv
14-01-2015
08:51 AM BST
 Hi, Our Team using Shared Excel file, file contain 1000 data in a row (7 to 8 users)
few user are using MS Excel 2003 & few are using MS Excel 2010
Many times it's happened file getting locked by some user.
Kindly confirm how to avoid such error & why such error is happened in sharing file.
Kindly suggest best possible solution & Thanks in advance...!!
My email id is Rajiv.rank1@tata-aig.com & rajiv.rank@gmail.com
2546
mathcode
29-09-2014
01:13 AM BST
I've been studying your numbers to words VBA routine. How could the routine be modified so that a number like 21.456, instead of being twenty one point four five six, could be twenty one and four hundred fifty six thousandths?
^               2547-2562 of 2562  2531-2546 >>

Print | RSS Views: 38598 (Unique: 11605 ) / Subscribers: 53 | What's this?