Results 1 to 8 of 8

Thread: Excel formula to extract first word

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by Klum2015; Mar 8th, 2019 at 12:59 AM.

  2. #2
    Board Regular
    Join Date
    Feb 2018
    Location
    Oz
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default 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.

  3. #3
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,379
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default 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

    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 by etaf; Mar 8th, 2019 at 01:39 AM.
    Wayne

    Using Excel Version 365 for Mac

  4. #4
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    
    
    FWAD = matches(0).submatches(0)
    
    
    End Function
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  5. #5
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,572
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel formula to extract first word

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

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,240
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default 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))

    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,572
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel formula to extract first word

    More concise UDF :
    Code:
    Function FWAD(s$)
    FWAD = Split(s, " ")(2)
    End Function
    Last edited by footoo; Mar 8th, 2019 at 02:54 AM.

  8. #8
    Board Regular
    Join Date
    Oct 2013
    Location
    Lakeland, FL USA
    Posts
    395
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel formula to extract first word

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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