Number to word

K

kashish

I have randomly single nos in column A,
How can I get word by formula in B column for particular no given in A column.

0 zero
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
 
K

kashish

Can it works like given below

A B
102 one zero two
50 five zero
32 three two
013 zero one three
 
J

Jacob Skaria

It is better to use a UDF to acheive this..If you are new to UDFs . launch
VBE using Alt+F11. Insert Module. Paste the below function. Close VBE and
return back to Workbook. Try as below

A1 = 1234567890
B1 = NumToText(A1)

Function NumToText(varValue) As String
arrWords = Array("Zero", "One", "Two", "Three", "Four", "Five", "Six",
"Seven", "Eight", "Nine")
If IsNumeric(varValue) = True Then
For intTemp = 1 To Len(varValue)
NumToText = NumToText & arrWords(Mid(varValue, intTemp, 1)) & " "
Next
End If
NumToText = Trim(NumToText)
End Function


If you are really looking for a formula; the below will work for 3 digit
numbers
=CHOOSE(LEN(A1),CHOOSE(A1+1,"Zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(LEFT(A1,1)+1,"Zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
& " " &
CHOOSE(RIGHT(A1,1)+1,"Zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(LEFT(A1,1)+1,"Zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
& " " &
CHOOSE(MID(A1,2,1)+1,"Zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
& " " &
CHOOSE(RIGHT(A1,1)+1,"Zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"))



If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top