Thread: Excel formula to extract first word Thanks: 0 Likes:  1 Post #5239339 (1)

1. Excel formula to extract first word

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  Reply With Quote

2. Re: Excel formula to extract first word

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.  Reply With Quote

3. Re: Excel formula to extract first word

=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

=IFERROR(MID(A1,SEARCH("-",A1,1)+2,SEARCH(" ",A1,SEARCH("-",A1,1)+2)-SEARCH("-",A1,1)-2),"")
for blank cells when copying down  Reply With Quote

4. Re: Excel formula to extract first word

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

End Function  Reply With Quote

5. Re: Excel formula to extract first word

Another way :
Code:
Function FWAD(Source As String)
Dim arr() As String
arr = Split(Source, " ")
End Function  Reply With Quote

6. Re: Excel formula to extract first word

Try this:
AB
1
2Happy - Data for Window.Data
3Grumpy - Sam is the one.Sam
4Happy - Apples for oranges. Apples

Hoja2

Worksheet Formulas
CellFormula
B2=TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("- ",A2)+2,99)," ",REPT(" ",99)),99))  Reply With Quote

7. Re: Excel formula to extract first word

More concise UDF :
Code:
FWAD = Split(s, " ")(2)
End Function  Reply With Quote

8. Re: Excel formula to extract first word

Enter formula in B1 and copy down
Code:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),100,50))  Reply With Quote

User Tag List

Tags for this Thread

apples, happy, result, sam, word  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•