Excel 2021 Custom Function with SEARCH does not work

Lisa Wilke-Thissen 90,725 Reputation points Volunteer Moderator
2026-08-15T17:56:33.81+00:00

Hi all,

I want to extract the first name from a combination of a last name and a first name.
With Excel 2024 following Lambda function works fine:

=LAMBDA(Text1,Right(Text1,Len(Text1)-Search(" ",Text1)))

However, Excel 2021 does not provide Lambda. Therefore, I tried to create a custom function with VBA:

Function FirstName(Text1)
FirstName = Right(Text1, Len(Text1) - Search(" ", Text1))
End Function

But a compiling error message appears regarding SEARCH ("Sub or Function not defined"). Unfortunately, I can't find a solution.

Best regards from Germany,

Lisa

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer accepted by question author
Marcin Policht 107.2K Reputation points MVP Volunteer Moderator
2026-08-15T20:31:02.17+00:00

Looks like the problem is that SEARCH is an Excel worksheet function, not a native VBA function. When you write VBA, you shouldn't call Search() directly, so VBA reports “Sub or Function not defined.” You can use the VBA equivalent, InStr, instead.

The VBA function would be:

Function FirstName(Text1) FirstName = Right(Text1, Len(Text1) - InStr(Text1, " ")) End Function

For example, if A1 contains Smith John, the Excel formula =FirstName(A1) will return John.

You can also explicitly call the Excel SEARCH function from VBA:

Function FirstName(Text1) FirstName = Right(Text1, Len(Text1) - Application.WorksheetFunction.Search(" ", Text1)) End Function

Consider using the InStr version because InStr is the native VBA function for finding the position of a character or string.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Was this answer helpful?

2 people found this answer helpful.

0 additional answers

Sort by: Newest
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.