+ Reply to Thread
Results 1 to 7 of 7

Formula to parse first name & surname from cell containing single word

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Formula to parse first name & surname from cell containing single word

    Just a tricky string manipulation

    MAIN PROBLEM:
    Say I have a list of persons names in Column A. I want formulas in Columns B & C to return the individuals First & Last names respectively. This is easy when the names are separated by a space/full stop/underscore but what if there is no separator?

    So cell A1 "JohnSmith" would need to return "John" in cell B1 and "Smith" in cell C1.

    I can think of two ways to approach this:
    1. Identify first use of Upper case character where it is preceded by a lower case character
    2. Identify the use of an Upper case character after the first letter in the string (to avoid picking up J in JohnSmith)


    SUB PROBLEM:
    Now a twist just to make it more challenging. What happens if we have the cell data as SurnameFirstName instead? The formulas would need to handle Surnames starting with "O'", "Mac" & "Mc"...


    So does anyone have a solution to the main problem?

    Maybe even the the sub problem as well?
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Formula to parse first name & surname from cell containing single word

    Please attach a sample workbook with the expected output for better understanding


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    09-23-2008
    Location
    Mexico
    Posts
    200

    Re: Formula to parse first name & surname from cell containing single word

    Maybe:
    http://www.mrexcel.com/forum/showthr...56#post2095356

    pgc01

    Please Login or Register  to view this content.
    or:
    http://www.mrexcel.com/forum/showthread.php?t=573462

    This CSE formula should do what you want
    =REPLACE(A1, 1+MIN(FIND(CHAR(64+COLUMN($A$1:$Z$1)),MID(A1,2,255)&(CHAR(64+COLUMN($A$1:$Z$1))))), 0, " ")

    It should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Formula to parse first name & surname from cell containing single word

    Quote Originally Posted by :) Sixthsense :) View Post
    Please attach a sample workbook with the expected output for better understanding
    Please see attached workbook.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Formula to parse first name & surname from cell containing single word

    Quote Originally Posted by Armando Montes View Post
    Maybe:
    Thanks for both tips. In this instance however I would prefer to avoid using VBscripts and arrays. :S

    I am looking for non-array formulas for columns B & C.
    Last edited by mc84excel; 04-18-2013 at 10:40 PM. Reason: clarify thread is asking for formula solution

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Formula to parse first name & surname from cell containing single word

    Sorry folks. I know I have posed a hard one . Separating strings with no delimiters is not something I would do by choice!


    Of interest:
    I have found a UDF solution (see http://www.mrexcel.com/forum/excel-q...-delimiter.htm) and it works fine on my main problem. (It fails on the Secondary problem posed however).

    Although the UDF solution is interesting, I am seeking a non-array formula solution that doesn't require any UDFs.

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Formula to parse first name & surname from cell containing single word

    I found this formula but it is not quite there.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    On the main problem posted the formula:
    1. fails on BobMcSurname.
    2. inserts a space delimiter rather than parsing into two columns.
    Last edited by mc84excel; 04-18-2013 at 10:52 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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