Try this:

Function FirstName(pData As String)
iPos1 = InStr(pData, " ")
If iPos1 = 0 Then
FirstName = pData
Else
FirstName = Left(pData, iPos1 - 1)
End If
End Function

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.

I prefer to use string functions like

-mid
-instr
-find
-replace

etc.

For example

Private Function FirstName(CellData as String)
FirstName = Mid(CellData,1,Instr(1,CellData,Chr(32)))
End Function

But one question. What if the name is "Michael J. Fox" or etc?

Maybe it should start to search from the end?