Convert text mixed numbers to spelling

Closed
sandeep8627
Posts
1
Registration date
Friday February 1, 2013
Status
Member
Last seen
February 1, 2013
- Feb 1, 2013 at 10:01 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Feb 2, 2013 at 03:03 PM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Feb 2, 2013 at 03:03 PM
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