convert text mixed numbers to spellingClosed

Question

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

rizvisa1 · Answer

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

Convert text mixed numbers to spelling

1 response

Similar discussions

Subscribe To Our Newsletter!