Excel formula to extract first word

[Klum2015]

Hi there,

I am trying to find a formula that will allow me to extract the first word after the dash - and leave out the rest of the string. Please advise.

Happy - Data for Window. Result I want is Data
Grumpy - Sam is the one. Result I want is Sam
Happy - Apples for oranges. Result I want is Apples

 

[DSCfromCFA]

G'day Klum2015,

I am a newbie, and can only do it in two steps.

Text to be extracted is in H16
This formula in I16 - =MID(H16,FIND("-",H16)+2,99) - FIND the MID-string dash character and starting two characters to the right, take the next 99 characters
This formula in J16 - =LEFT(I16,FIND(" ",I16)) - from the result of the above formula, find the first space character and leave everything to the LEFT of it.

I await with interest how much simpler an experienced user will make it.

 

[etaf]

=MID(A1,SEARCH("-",A1,1)+2,SEARCH(" ",A1,SEARCH("-",A1,1)+2)-SEARCH("-",A1,1)-2)

i suspect may be simplified
assuming text is in A1

search for the - and add 2 - that assumes the - is always followed by a space as shown in the example
using MID()
thats the starting number of the text to extract

Now we search for the 1st space after the - and space

so in the length of text
SEARCH(" ",A1,SEARCH("-",A1,1)+2)-SEARCH("-",A1,1)-2)
we look for the 1st space after the - plus a space
that tells us its position from the start and so we then need to take off the length from the start to after the - plus space

add an IFERROR()
=IFERROR(MID(A1,SEARCH("-",A1,1)+2,SEARCH(" ",A1,SEARCH("-",A1,1)+2)-SEARCH("-",A1,1)-2),"")
for blank cells when copying down

 

[lrobbo314]

A regular expression UDF solution....

Function FWAD(s As String) As String
Dim RX As Object: Set RX = CreateObject("VBScript.REGEXP")
Dim pat As String: pat = "\s\-\s(\w+)\s"


With RX
    .Global = True
    .ignorecase = True
    .MultiLine = True
    .Pattern = pat
    Set matches = .Execute(s)
End With


FWAD = matches(0).submatches(0)


End Function

 

[footoo]

Another way :

Function FWAD(Source As String)
Dim arr() As String
arr = Split(Source, " ")
FWAD = arr(2)
End Function

 

[DanteAmor]

Try this:

Book1
	A	B
1
2	Happy - Data for Window.	Data
3	Grumpy - Sam is the one.	Sam
4	Happy - Apples for oranges.	Apples
Hoja2

Cell Formulas
Range	Formula
B2	=TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("- ",A2)+2,99)," ",REPT(" ",99)),99))

 

[footoo]

More concise UDF :

Function FWAD(s$)
FWAD = Split(s, " ")(2)
End Function

 

[AlKey]

Enter formula in B1 and copy down

[B]=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),100,50))[/B]