Grabbing text from just part of a cell

[Mary T]

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

 

[Guest]

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

 

[Mary T]

That worked great, thanks!