The following function will grab whatever follows the colon space:

Function SubjectNum(InitialValue As String) As String

Dim l As Long

'Find the location of the colon and add 1 for the space
l = InStr(1, InitialValue, ":") + 1

SubjectNum = Right$(InitialValue, Len(InitialValue) - l)

End Function

--
Kevin Backmann


"Mary T" wrote:


Hi all,

I have sheets that come in that include a cell that says, for instance,
'Subject no.: 5'. How would I grab just the '5' part of that cell?

Help appreciated, thanks!


--
Mary T
------------------------------------------------------------------------
Mary T's Profile: http://www.excelforum.com/member.php...o&userid=30151
View this thread: http://www.excelforum.com/showthread...hreadid=498349