Extract Left Most Characters Until a Space

[Malcy_J]

I am trying to write a function that finds and breaks a cell about the first space.
Worksheet is:
=LEFT(name,FIND(" ",name)-1)

Have tried...

Function Firstname(name) 
     
    Dim Space As Integer 
     
    Set Space = .Find(" ", name) 
     
    Firstname = Left(name, Space) 
     
End Function 

and also...

Function Firstname(name) 
     
    firstname = left(name,find(" ",name)-1)) 
     
End Function 

What am i doing wrong?

Cheers

[Dave Hawley]

POOR THREAD TITLE Please read THIS PAGE and THIS PAGE IN FULL

[Malcy_J]

how is my question not accurate? I want to know the VBA equivalent of
=find(" ",A2)

I don't think that I could ask any clearer than that.

[Dave Hawley]

See the new thread title which is along the lines of what you agreed to. Hvae you read the rules?

[Malcy_J]

Ok, sweet. Get ya


BUT


what am I looking for thats going to make that work in VBA? I want my staff/users to simply right a formula sort of along the lines:

=firstname(name)
=lastname(name)

I get all the left, right etc, but am not sure how to find the first space.

Cheers.

[Dave Hawley]

Try

Function FirstName(Cell_Name As Range) As String 
    Dim lSpace As Long 
    Dim strName As String 
     
    strName = Trim(Cell_Name) 
     
    lSpace = InStr(1, strName, " ", vbTextCompare) 
     
    FirstName = Trim(Left(strName, lSpace)) 
     
End Function 

[Malcy_J]

Most excellent!!

Thanks a whole bunch!



Made the right argument now.

Function LastName(Cell_Name As Range) As String 
    Dim lSpace As Long 
    Dim strName As String 
     
    strName = Trim(Cell_Name) 
     
    lSpace = InStr(1, strName, " ", vbTextCompare) 
     
    LastName = Trim(Right(strName, Len(strName) - lSpace)) 
     
End Function