Spell number Function in excel

[Dr. Pinki Insan]

How to convert figures into words by excel formula

Press Alt+f11 key

Press insert and select module 

paste the below matter in the window and save.

Function SpellNumber(amt As Variant) As Variant

Dim FIGURE As Variant

Dim LENFIG As Integer

Dim i As Integer

Dim WORDs(19) As String

Dim tens(9) As String

WORDs(1) = "ONE"

WORDs(2) = "TWO"

WORDs(3) = "THREE"

WORDs(4) = "FOUR"

WORDs(5) = "FIVE"

WORDs(6) = "SIX"

WORDs(7) = "SEVEN"

WORDs(8) = "EIGHT"

WORDs(9) = "NINE"

WORDs(10) = "TEN"

WORDs(11) = "ELEVEN"

WORDs(12) = "TWELVE"

WORDs(13) = "THIRTEEN"

WORDs(14) = "FOURTEEN"

WORDs(15) = "FIFTEEN"

WORDs(16) = "SIXTEEN"

WORDs(17) = "SEVENTEEN"

WORDs(18) = "EIGHTEEN"

WORDs(19) = "NINETEEN"

tens(2) = "TWENTY "

tens(3) = "THIRTY "

tens(4) = "FOURTY "

tens(5) = "FIFTY "

tens(6) = "SIXTY "

tens(7) = "SEVENTY "

tens(8) = "EIGHTY "

tens(9) = "NINETY "

FIGURE = amt

FIGURE = Format(FIGURE, "FIXED")

FIGLEN = Len(FIGURE)

If FIGLEN < 12 Then

FIGURE = Space(12 - FIGLEN) & FIGURE

End If

If Val(Left(FIGURE, 9)) > 1 Then

ElseIf Val(Left(FIGURE, 9)) = 1 Then

End If

For i = 1 To 3

If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) > 19 Then

SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))

SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))

End If

If i = 1 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & " CRORE "

ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & " LAKH "

ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & " THOUSAND "

End If

FIGURE = Mid(FIGURE, 3)

Next i

If Val(Left(FIGURE, 1)) > 0 Then

SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 1))) + " HUNDRED "

End If

FIGURE = Mid(FIGURE, 2)

If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) > 19 Then

SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))

SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))

End If

FIGURE = Mid(FIGURE, 4)

If Val(FIGURE) > 0 Then

SpellNumber = SpellNumber & " PAISE "

If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) > 19 Then

SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))

SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))

End If

End If

FIGURE = amt

FIGURE = Format(FIGURE, "FIXED")

If Val(FIGURE) > 0 Then

SpellNumber = SpellNumber & " ONLY "

End If

End Function

This will enable you to convert the figures into words.

Dr. Pinki Insan
Asstt. Professor
Indira Gandhi University
Meerpur, Rewari, Haryana
India

[Neeraj Kaushik]

Great work Mam

Keep posting such wonderful info.

Regards

Neeraj

--
Protocols of this Group:
 
1. Plz search previous post in group before posing the question.
2. Don't write query in someone's post. Always use the option of New topic for the new question. You can do this by writing to dataanalys...@googlegroups.com
3. Its better to give a proper subject to your post/query. It'll help others while searching.
4. Never write Open ended queries. This group intend to help research scholars NOT FOR WORK THEM.
5. Never write words like URGENT in ur posts. People will help them when they are free.
6. Never upload any info about National Seminars/Conferences. Send such info on personal emails. And feel free to share any RESEARCH related info.
7. No Happy New Year, Happy Diwali, Happy Holi, Happy B'day, Happy Anniversary etc. allowed on this group.
8. Few months back there was a facility for asking & sharing the Research Papers. Now there is no provision of asking for the research paper here.
 
Let’s make a better research environment.
---
You received this message because you are subscribed to the Google Groups "DataAnalysis" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataanalysistrai...@googlegroups.com.
To post to this group, send email to dataanalys...@googlegroups.com.
Visit this group at https://groups.google.com/group/dataanalysistraining.
For more options, visit https://groups.google.com/d/optout.

[Amit Manglani]

Some more clarification required to use it.

We use the same kind of technique to fill the foils and counter-foils.

But here, 

1. in what name the module should be saved?

2. where it should be saved?

3. What function is needed to type to convert numerals in alphabets?

--

Regards,



Amit Manglani

Assistant Professor
Department of Commerce,
School of Management and Commerce,
Guru Ghasidas Vishwavidyalaya (GGV) [A Central University]
Koni, Bilaspur
Chhattisgarh - 495 009
India

University website: www.ggu.ac.in 
E-mail: amit.m...@gmail.com
_____________________________________________________________________

"You have to work with people who make you feel insecure. Because they push you to work better. It is part of inherent learning."

~ Sundar Pichai (CEO, Google) @ SRCC

[rahul dhiman]

Request to complete the information in the trailing mail. It is still not clear that after pasting the functions how to save and where to save?then what to do next to convert the numbers to words?

Please clarify!!!

With Regards,

Rahul Dhiman
Research Scholar,
National Institute of Technology,
Hamirpur. ( Himachal Pradesh)
PIN : 177 005
Mobile : +91-9882146140

[Neeraj Kaushik]

Gud mrng Friends

I've shared one of my very old mail here where all steps are given in detail for converting word to number.

Actually it need a new macro which can be made in Excel itself.

All steps are given in detail inn the attachment.

Plz feel free to ask for any/every doubt.

Best wishes

Neeraj

[Dr. Pinki Insan]

Dear Scholars

Please go through the snapshots in attachment which will make you understand the whole process of spellnumber.

Thanks

To unsubscribe from this group and stop receiving emails from it, send an email to dataanalysistraining+unsub...@googlegroups.com.

To post to this group, send email to dataanalys...@googlegroups.com.
Visit this group at https://groups.google.com/group/dataanalysistraining.
For more options, visit https://groups.google.com/d/optout.

--
Protocols of this Group:
 
1. Plz search previous post in group before posing the question.
2. Don't write query in someone's post. Always use the option of New topic for the new question. You can do this by writing to dataanalys...@googlegroups.com
3. Its better to give a proper subject to your post/query. It'll help others while searching.
4. Never write Open ended queries. This group intend to help research scholars NOT FOR WORK THEM.
5. Never write words like URGENT in ur posts. People will help them when they are free.
6. Never upload any info about National Seminars/Conferences. Send such info on personal emails. And feel free to share any RESEARCH related info.
7. No Happy New Year, Happy Diwali, Happy Holi, Happy B'day, Happy Anniversary etc. allowed on this group.
8. Few months back there was a facility for asking & sharing the Research Papers. Now there is no provision of asking for the research paper here.
 
Let’s make a better research environment.
---
You received this message because you are subscribed to the Google Groups "DataAnalysis" group.

To unsubscribe from this group and stop receiving emails from it, send an email to dataanalysistraining+unsub...@googlegroups.com.

To unsubscribe from this group and stop receiving emails from it, send an email to dataanalysistraining+unsub...@googlegroups.com.

To post to this group, send email to dataanalys...@googlegroups.com.
Visit this group at https://groups.google.com/group/dataanalysistraining.
For more options, visit https://groups.google.com/d/optout.

[salijeet...@gmail.com]

On Tuesday, February 16, 2016 at 11:56:11 PM UTC+5:30, Dr. Pinki Insan wrote:
> How to convert figures into words by excel formula
>
>
>
>
>
>

You