Excel formula to extract first word

Klum2015

New Member
Joined
Feb 3, 2016
Messages
10
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
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
=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
 
Last edited:
Upvote 0
A regular expression UDF solution....


Code:
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
 
Upvote 0
Another way :
Code:
Function FWAD(Source As String)
Dim arr() As String
arr = Split(Source, " ")
FWAD = arr(2)
End Function
 
Upvote 0
Try this:

Book1
AB
1
2Happy - Data for Window.Data
3Grumpy - Sam is the one.Sam
4Happy - Apples for oranges.Apples
Hoja2
Cell Formulas
RangeFormula
B2=TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("- ",A2)+2,99)," ",REPT(" ",99)),99))
 
Upvote 0
More concise UDF :
Code:
Function FWAD(s$)
FWAD = Split(s, " ")(2)
End Function
 
Last edited:
Upvote 0
Enter formula in B1 and copy down
Code:
[B]=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),100,50))[/B]
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top