UDF Function to extract first word in a string

[freonthewhite]

Hi, I'm having trouble doing the VBA to create my own function. I have the excel formula to do it, but I just want to be able to have it be a function so that I can do something like =FirstWord(A2)
The excel formula that does this is:
=IF(ISERR(FIND(" ",A2)),A2, LEFT(A2, FIND(" ",A2)-1))

So I just want the function to feed the cell reference so that instead of using this formula I can just do =FirstWord(A2)

I tried the below, but it looks like I can't just do what I did.

Any help is appreciated, thank you

Option Explicit

Function FirstWord(input_data)

    input_data = IF(ISERR(FIND(" ",input_data)),input_data, LEFT(input_data, FIND(" ",input_data)-1))
       
    FirstWord = input_data(word)
    
End Function

 

[hungtbatman1]

Function FirstWord(ByVal input_data As String) As String
    input_data = Trim(input_data) & " "      ' So that FirstWord("London") --> "London"
    FirstWord = Left(input_data, InStr(1, input_data, " ") - 1)
End Function

 

[Fluff]

Why not just use the textbefore function

Fluff.xlsm
	A	B
1
2	Lindley	Lindley
3	Penistone East	Penistone
4	Longholme	Longholme
5	Hyde Werneth	Hyde
Data

Cell Formulas
Range	Formula
B2:B5	B2	=TEXTBEFORE(A2," ",,,1)

 

[freonthewhite]

Why not just use the textbefore function

Fluff.xlsm
	A	B
1
2	Lindley	Lindley
3	Penistone East	Penistone
4	Longholme	Longholme
5	Hyde Werneth	Hyde
Data

Cell Formulas
Range	Formula
B2:B5	B2	=TEXTBEFORE(A2," ",,,1)

Too many arguments, I wanted it to be really quick and efficient.

 

[freonthewhite]

Function FirstWord(ByVal input_data As String) As String
    input_data = Trim(input_data) & " "      ' So that FirstWord("London") --> "London"
    FirstWord = Left(input_data, InStr(1, input_data, " ") - 1)
End Function

Thank you, this looks great, but for some reason I can't use it by just doing =frankshort(A1)
If I look in the list for the user defined functions then it shows up like in the picture. It will work if I pick it this way, but it pops up a window asking for the input.
Any idea why that is?

 

[Fluff]

I wanted it to be really quick and efficient.

It will be faster & more efficient than a UDF.

 

[Fluff]

If you want the udf in your personal macro workbook you need to type

=personal.xlsb!frankshort(A1)

 

[freonthewhite]

It will be faster & more efficient than a UDF.

For you and me yes, but the people that I'm going to be setting this up for are people who struggle with copy/paste, that's why I'm trying to make it as easy as possible.

 

[freonthewhite]

If you want the udf in your personal macro workbook you need to type

=personal.xlsb!frankshort(A1)

Dang, so it can't be just

=frankshort(A1)

if it's in the persconal workbook?

 

[hungtbatman1]

If you use the "Insert Function" window, this happens, not only with frankshort but with every Excel function, e.g. Find. Click where you want to enter the formula, e.g. B1 --> type =f. With every Excel function, the same, you have to remember what the function is called, e.g. SUBS***. And then you type =s. So when you type =f, a list opens, where you see the function frankshort. Select this function ...