Convert text mixed numbers to spelling [Closed]

Report
Posts
1
Registration date
Friday February 1, 2013
Status
Member
Last seen
February 1, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,


i want to know how can we convert in excel the mixed numbers such as 1ZXV78 to "one ZXV seven eight".. can some one help on this task,



Thanks,
Del

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You have to use a user defined function

try this


Public Function changeNumberToText(Cell As Range) As String
   Dim newValue As String
   
   changeNumberToText = vbNullString
   If (Cell Is Nothing) Then Exit Function
   If (Cell.Cells.Count <> 1) Then Exit Function
   
   newValue = Cell
   newValue = Replace(newValue, " ", "|")
   newValue = Replace(newValue, 0, " Zero ")
   newValue = Replace(newValue, 1, " One ")
   newValue = Replace(newValue, 2, " Two ")
   newValue = Replace(newValue, 3, " Three ")
   newValue = Replace(newValue, 4, " Four ")
   newValue = Replace(newValue, 5, " Five ")
   newValue = Replace(newValue, 6, " Six ")
   newValue = Replace(newValue, 7, " Seven ")
   newValue = Replace(newValue, 8, " Eight ")
   newValue = Replace(newValue, 9, " Nine ")
   newValue = Replace(newValue, "  ", " ")
   newValue = Trim(newValue)
   newValue = Replace(newValue, "|", " ")
   
   newValue = Replace(newValue, 1, "One ")
   changeNumberToText = newValue

End Function
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!