+ Reply to Thread
Results 1 to 9 of 9

Formula to pull first word out of string of text

  1. #1
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Formula to pull first word out of string of text

    Hi,

    In the attached workbook, i need to pull out the first word of the string and populate column C with it but i need a formula to do this?

    Any ideas, the only thing i can think of is using the Left function but that won't work as the word length is different for each line...
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to pull first word out of string of text

    =left(a2,find(" ",a2)-1)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula to pull first word out of string of text

    Hi

    =LEFT(A2,FIND(" ",A2)-1)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Formula to pull first word out of string of text

    Many thanks

  5. #5
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Formula to pull first word out of string of text

    Would it be possible to add this to an if statement, to say if cell AH6 = EIO then LEFT(A2,FIND(" ",A2)-1)??

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to pull first word out of string of text

    yes but what do you actually mean?
    if($AH$6="EIO",LEFT(A2,FIND(" ",A2)-1),a2) ???????

  7. #7
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Formula to pull first word out of string of text

    That works, thank you. I only wanted the formula to activate if a column contained the letters EIO.

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to pull first word out of string of text

    all the column ah:ah?

    if(countif($AH$:$AH$,"EIO"),LEFT(A2,FIND(" ",A2)-1),a2)

  9. #9
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: Formula to pull first word out of string of text

    That's great.

    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to return a word from string of text
    By kenadams378 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-28-2013, 05:17 AM
  2. Replies: 2
    Last Post: 05-28-2013, 12:41 AM
  3. Want excel formula/macro to pull external text string from WORD and display in excel
    By crnadeau4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 12:49 PM
  4. [SOLVED] How to conditionally insert text string into a line after first word using Word 2010
    By staggers47 in forum Word Formatting & General
    Replies: 5
    Last Post: 12-08-2012, 11:07 AM
  5. Formula to remove 2nd word from a string of text
    By bikramksingh in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-25-2010, 04:02 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1