+ Reply to Thread
Results 1 to 4 of 4

formula for 'pick out the first word of the cell up until the space'

  1. #1
    Forum Contributor
    Join Date
    12-11-2013
    Location
    St Moritz, GR, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    187

    formula for 'pick out the first word of the cell up until the space'

    I've a list in which the first word is an alpha-numeric-punctuation string, and then the rest is plain English. How do I pick out the first 'word'? As in 'say everything in A1 up until the space'?

    eg
    1234 Wooden Giblets
    3AP--0 Timber Doves

    When I want it to say

    1234
    3AP--0

    Demo uploaded.
    Attached Files Attached Files

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: formula for 'pick out the first word of the cell up until the space'

    You may try:
    =LEFT(A1,FIND(" ",A1&" ")-1)
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  3. #3
    Forum Contributor
    Join Date
    12-11-2013
    Location
    St Moritz, GR, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: formula for 'pick out the first word of the cell up until the space'

    That works. I get about half of that formula:

    FIND is get the space in A1
    LEFT is something about getting the text out of that cell.

    Ta.

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: formula for 'pick out the first word of the cell up until the space'

    FIND locates position of space (I have added &" " in case text may not have space in) and LEFT will return all text before this position because of -1 part.

+ 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. pick up the word from right end
    By reedzhou in forum Excel General
    Replies: 5
    Last Post: 07-14-2011, 06:38 AM
  2. Excel 2007 : Pick-List pick fills in another cell
    By hpasso217 in forum Excel General
    Replies: 0
    Last Post: 02-28-2011, 05:38 PM
  3. Replies: 13
    Last Post: 05-06-2009, 07:33 AM
  4. formula to pick cell color
    By gurpreet0802 in forum Excel General
    Replies: 1
    Last Post: 01-20-2009, 06:34 AM
  5. [SOLVED] How do I clear blank space at the top of a word-wrapped text cell?
    By mirage1210 in forum Excel General
    Replies: 2
    Last Post: 02-11-2005, 06:06 PM

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