+ Reply to Thread
Results 1 to 11 of 11

Error Extracting first name from cell if only 1 name

  1. 01-01-2014, 08:00 PM #1
    dominick1
    dominick1 is offline
    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
    Reply With QuoteRegister To Reply
  2. 01-01-2014, 08:06 PM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

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

    Try this version

    =LEFT(A2,FIND(" ",A2&" ")-1)
    Audere est facere
    Reply With QuoteRegister To Reply
  3. 01-01-2014, 08:10 PM #3
    dominick1
    dominick1 is offline
    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.
    Reply With QuoteRegister To Reply
  4. 01-01-2014, 08:19 PM #4
    dominick1
    dominick1 is offline
    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.
    Reply With QuoteRegister To Reply
  5. 01-01-2014, 08:46 PM #5
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,366

    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)))
    Reply With QuoteRegister To Reply
  6. 01-01-2014, 08:49 PM #6
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

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

    Try this version

    =IFERROR(REPLACE(A2,1,FIND(" ",A2),""),"")
    Reply With QuoteRegister To Reply
  7. 01-01-2014, 08:51 PM #7
    dominick1
    dominick1 is offline
    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.
    Reply With QuoteRegister To Reply
  8. 01-01-2014, 08:54 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    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
    Reply With QuoteRegister To Reply
  9. 01-01-2014, 09:05 PM #9
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    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
    Reply With QuoteRegister To Reply
  10. 01-02-2014, 05:00 AM #10
    rishikrsaw
    rishikrsaw is offline
    Forum Contributor
    Join Date
    11-12-2013
    Location
    Delhi
    MS-Off Ver
    Excel 2016
    Posts
    135

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

    =iferror(substitute(a2,left(a2,find(" ",a2)),""),"")
    Reply With QuoteRegister To Reply
  11. 01-02-2014, 05:07 AM #11
    Izandol
    Izandol is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. [SOLVED] 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1