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 2821 users have said thank you to us this month