MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel 2002 UDF

  Asked By: Jada    Date: Mar 14    Category: MS Office    Views: 992

I have a formula I want to turn into a user defined function. The formula is
=LEFT(A1,FIND(" ",A1)-1)

Where cell A1 contains a name like John Smith, the formula will return the
text string "John".

What I want to do is create a user defined function in VBA, so I can use a
formula that might look like:


Could any kind person out there show me what the VBA code would look like
for this function?



3 Answers Found

Answer #1    Answered By: Lois Schmidt     Answered On: Mar 14

Try this:

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

Answer #2    Answered By: Abaigael Cohen     Answered On: Mar 14

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.

Answer #3    Answered By: Abella Martin     Answered On: Mar 14

I prefer to use string functions like



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?

Didn't find what you were looking for? Find more on Excel 2002 UDF Or get search suggestion and latest updates.