Two ways (out of numerous)

Option Explicit

Public Function Version1(ByVal Cell As Range) As String
Version1 = Left(Cell.Value, InStr(Cell.Value, " ") - 2)
End Function

Public Function Version2(ByVal Cell As Range) As String
Dim Parts
Parts = Split(Cell.Value, " ")
Version2 = Parts(0)
End Function

Both assume that there is a space in there somewhere. If not, then the first
returns #VALUE (as indeed does your formula). The second assumes that the
person only has a first name and returns that.