+ Reply to Thread
Results 1 to 11 of 11

Error Extracting first name from cell if only 1 name

  1. #1
    Registered User
    Join Date
    12-30-2013
    Location
    US
    MS-Off Ver
    Excel 2011 Mac
    Posts
    19

    Error Extracting first name from cell if only 1 name

    I'm trying to extract the first name from each row in column a using:

    Formula: copy to clipboard
    Please Login or Register  to view this content.




    A B
    Joe Smith Joe
    Julie Smith Julie
    Julie #VALUE!


    If the cell does not have 2 names it's giving me an error. Any help with how I could correct this is greatly appreciated. Thanks

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Error Extracting first name from cell if only 1 name

    Try this version

    =LEFT(A2,FIND(" ",A2&" ")-1)
    Audere est facere

  3. #3
    Registered User
    Join Date
    12-30-2013
    Location
    US
    MS-Off Ver
    Excel 2011 Mac
    Posts
    19

    Re: Error Extracting first name from cell if only 1 name

    Quote Originally Posted by daddylonglegs View Post
    Try this version

    =LEFT(A2,FIND(" ",A2&" ")-1)
    Thank you, this corrected my issue.

  4. #4
    Registered User
    Join Date
    12-30-2013
    Location
    US
    MS-Off Ver
    Excel 2011 Mac
    Posts
    19

    Re: Error Extracting first name from cell if only 1 name

    I apologize, I have one more question regarding this. If I wanted to do the opposite (Extract the second name only) with my table. How would the formula be setup so it would only show the second name. And if there is no second name the cell is empty. Thank you again.

  5. #5
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,263

    Re: Error Extracting first name from cell if only 1 name

    =if(iserror(right(a2,len(a2)-search(" ",a2,1))),"",right(a2,len(a2)-search(" ",a2,1)))

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Error Extracting first name from cell if only 1 name

    Try this version

    =IFERROR(REPLACE(A2,1,FIND(" ",A2),""),"")

  7. #7
    Registered User
    Join Date
    12-30-2013
    Location
    US
    MS-Off Ver
    Excel 2011 Mac
    Posts
    19

    Re: Error Extracting first name from cell if only 1 name

    Quote Originally Posted by azumi View Post
    =if(iserror(right(a2,len(a2)-search(" ",a2,1))),"",right(a2,len(a2)-search(" ",a2,1)))
    Thank you very much, this works perfect.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,891

    Re: Error Extracting first name from cell if only 1 name

    or this slightly shorter option...
    =IF(ISNUMBER(SEARCH(" ",A1,1)),MID(A1,SEARCH(" ",A1,1)+1,99),"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Error Extracting first name from cell if only 1 name

    hi dominick. yet another alternative:
    =IFERROR(MID(A2,FIND(" ",A2)+1,LEN(A2)),"")

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  10. #10
    Forum Contributor
    Join Date
    11-12-2013
    Location
    Delhi
    MS-Off Ver
    Excel 2007 2010 2013
    Posts
    133

    Re: Error Extracting first name from cell if only 1 name

    =iferror(substitute(a2,left(a2,find(" ",a2)),""),"")

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

    Re: Error Extracting first name from cell if only 1 name

    Also:
    =TRIM(MID(A2&" ",FIND(" ",A2&" "),255))
    • 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

+ 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. VBA syntax error on extracting loop
    By wolfram13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2013, 02:20 PM
  2. [SOLVED] #NUM! Error with Extracting Data Based on Criteria
    By ACM2 in forum Excel General
    Replies: 15
    Last Post: 06-28-2012, 02:38 PM
  3. Macro Error : Extracting last used cell
    By rgomez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-20-2011, 12:26 PM
  4. [SOLVED] Extracting data from Access - error encountered
    By Daniel Bonallack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2006, 02:20 AM
  5. Extracting data from Access - error encountered
    By Daniel Bonallack in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-01-2006, 07:40 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