I added this code in VB editor, but in the "Insert Function" window, I am not
able to see 'User defined functions" option.

"Gary''s Student" wrote:

Try these three User Defined Functions:

Function firstnumber(r As Range) As Variant
v = r.Value
l = Len(v)
firstnumber = ""
For i = 1 To l
ch = Mid(v, i, 1)
If IsNumeric(ch) Then
firstnumber = firstnumber & ch
Else
Exit For
End If
Next
End Function


Function centertext(r As Range) As Variant
v = r.Value
l = Len(v)
centertext = ""
For i = 1 To l
ch = Mid(v, i, 1)
If Not IsNumeric(ch) Then
centertext = centertext & ch
End If
Next
End Function


Function lastnumber(r As Range) As Variant
v = r.Value
l = Len(v)
gather = False
For i = 1 To l
ch = Mid(v, i, 1)
If Not IsNumeric(ch) Then
gather = True
Else
If gather Then
lastnumber = lastnumber & ch
End If
End If
Next
End Function


With data in A1, use as:

=firstnumber(A1)
=centertext(A1)
=lastnumber(A1)
--
Gary''s Student - gsnu200796