+ Reply to Thread
Results 1 to 4 of 4

Name Split receiving #VALUE! error

  1. #1
    Registered User
    Join Date
    11-18-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2016
    Posts
    31

    Name Split receiving #VALUE! error

    I have a column of names to split but I can't use "text to columns" because of credentials, etc. I've tried the following formula and it works where I have a first and last name available, but how do I get the first name to show if there is no last name, and leave the last name cell blank? Please see attached.
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Name Split receiving #VALUE! error

    Try this:

    =LEFT(A1,FIND(" ",A1&" ")-1)

    and

    =RIGHT(A1,MAX(0,LEN(A1)-FIND(" ",A1&" "))) or =MID(A1,FIND(" ",A1&" "),100)

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Name Split receiving #VALUE! error

    Hi Kazphillips,

    See the attached file where I have used Iserror function.. thanks.
    Name sample.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  4. #4
    Registered User
    Join Date
    11-18-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2016
    Posts
    31

    Re: Name Split receiving #VALUE! error

    They both work. Thanks so much!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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