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

 

[Fluff]

@hungtbatman1 that does not work if the function is in a different workbook (unless it's an xlam file)

 

[Fluff]

Dang, so it can't be just
=frankshort(A1) if it's in the persconal workbook?

Nope

 

[Fluff]

The other option is to put the UDF into an add-in file (ie an xlam) and distribute that.

 

[hungtbatman1]

@hungtbatman1 that does not work if the function is in a different workbook (unless it's an xlam file)

Oh yes, the function is in PERSONAL, so =frankshort(A1) doesn't work. I usually use add-in

 

[Dave Patton]

TextBefore.xlsm
	A	B	C
1
2	Lindley	Lindley	Lindley
3	Penistone East		Penistone
4	Longholme		Longholme
5	Hyde Werneth		Hyde
6	This is an example.		This
7
4b

Cell Formulas
Range	Formula
B2	B2	=FirstWord(A2)
C2:C6	C2	=FirstWord(A2:A6)
Dynamic array formulas.

Use Lambda and name the function.
You can include the function in an xlsx file and it will work anywhere in the file.
You could include the function in a template.
If you move the sheet with the function into another file, the function will also move.
The function prompts for the input, "CellRef"
To manually create the function, move to Formulas | Name Manager and enter the following:
Name FirstWord
Refers to: =LAMBDA(CellRef,TEXTBEFORE(CellRef," ",,,1))

 

[Macropod]

Code-wise, all you should need to get any word from an ordinary string is a UDF like:

Function GetWord(Rng As Range, i As Long) As String
GetWord = Split(Trim(Rng.Text), " ")(i - 1)
End Function

with a formula like:
=GetWord(A1,1)
to retrieve a given word, where '1' is the word to be retrieved.

 

[Peter_SSs]

but the people that I'm going to be setting this up for ..

Dang, so it can't be just ...
... if it's in the persconal workbook?

So, even if it did work in 'Personal' were you going to set it up in the 'Personal' workbook for each of those users?
Doesn't sound like the "easy" you were aiming for.

Too many arguments,

So if you were still looking for the UDF approach I would suggest this

Function FirstWord(s As String) As String
  FirstWord = Split(LTrim(s), " ")(0)
End Function

However, depending on how widely the users need this, & if you didn't want to create an Add-In, it may be that the LAMBDA approach suggested by @Dave Patton might be best. Below I have used an adaptation of that in case not all potential users have the TEXTBEFORE function (but they still would need the LAMBDA function).
It is set up in name Manger like this

Then in the worksheet used on an individual cell or group of cells as follows.

freonthewhite.xlsm
	A	B	C
1	London City	London	London
2	London		London
3	London Town		London
First word

Cell Formulas
Range	Formula
B1	B1	=FirstWord(A1)
C1:C3	C1	=FirstWord(A1:A3)
Dynamic array formulas.

Lambda Functions
Name	Formula
FirstWord	=LAMBDA(CellRef,LEFT(TRIM(CellRef),FIND(" ",TRIM(CellRef)&" ")-1))