+ Reply to Thread
Results 1 to 6 of 6

Text function to swap first and last name with something else to be deleted in the cell

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2003
    Posts
    30

    Text function to swap first and last name with something else to be deleted in the cell

    I have a series of names on my spreadsheet I'm running. Currently I have a cell that says "Carter, Jeff - LA." I want it to read "Jeff Carter."

    Other examples:
    Steen, Alexander - STL = Alexander Steen
    Sharp, Patrick - CHI = Patrick Sharp
    Duchene, Matt - COL = Matt Duchene

    I have a formula I can use to reorder the names but I don't know how to drop all the stuff after the last letter of the players first name.
    Last edited by letangerang58; 09-28-2014 at 12:50 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Text function to swap first and last name with something else to be deleted in the cel

    Hi,

    One way

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

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-18-2013
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Text function to swap first and last name with something else to be deleted in the cel

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    One way

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    That sorta worked. Now it's reading Jeff, Carter. Anyway to drop that comma?

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Text function to swap first and last name with something else to be deleted in the cel

    Try this

    =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",25)),25,25))&" "&LEFT(A1,FIND(",",A1)-1)

    A
    B
    1
    Steen, Alexander - STL Alexander Steen
    2
    Sharp, Patrick - CHI Patrick Sharp
    3
    Duchene, Matt - COL Matt Duchene
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    03-18-2013
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Text function to swap first and last name with something else to be deleted in the cel

    Perfect thanks!

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Text function to swap first and last name with something else to be deleted in the cel

    Quote Originally Posted by letangerang58 View Post
    That sorta worked. Now it's reading Jeff, Carter. Anyway to drop that comma?
    Yes, change the ", " but to

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


    i.e.

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

+ 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. Replies: 3
    Last Post: 07-10-2014, 12:06 PM
  2. Default text in 1 cell when blank - to remain once subsequent text deleted
    By thedude1222 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-13-2013, 08:45 AM
  3. Check Box to swap between having a formula in a cell and displaying text
    By anit999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2013, 04:59 AM
  4. [SOLVED] Swap text in a cell complication
    By alexxshadows in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-14-2012, 09:06 PM
  5. text is deleted when cell is edited
    By GRIFFO in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-23-2005, 11:05 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