Results 1 to 5 of 5

how to put a comma between last name and first name but not first name and MI

This is a discussion on how to put a comma between last name and first name but not first name and MI within the Excel Questions forums, part of the Question Forums category; Scenario: A column of names that have last name space first name space and then a middle name or initial ...

  1. #1
    Board Regular dbwiz's Avatar
    Join Date
    Nov 2007
    Location
    California
    Posts
    275

    Smile how to put a comma between last name and first name but not first name and MI

    Scenario: A column of names that have last name space first name space and then a middle name or initial have to be converted to comma delimited text for uploading into a program. So for example if the Excel column has the names

    Smith Joseph Edgar
    Doe Sally J

    when converted it needs to look like

    Smith, Joseph Edgar
    Doe, Sally J

    I tried Edit Replace and Find all, I hit the tab key once and replaced with , but got

    Smith, Joseph, Edgar,,,,,,

    So that didn't work. Is there an easy way to do this?

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,150

    Default Re: how to put a comma between last name and first name but not first name and MI

    Here's one way, there may be better ways.
    Assuming your name is in A1.....
    Code:
    =LEFT(A1,FIND(" ",A1,1)-1)&","&MID(A1,FIND(" ",A1,1),255)
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular dbwiz's Avatar
    Join Date
    Nov 2007
    Location
    California
    Posts
    275

    Talking Re: how to put a comma between last name and first name but not first name and MI

    Thanks! It works great and was easy to use!

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,969

    Default Re: how to put a comma between last name and first name but not first name and MI

    Here's another way....

    =SUBSTITUTE(A1," ",", ",1)

  5. #5
    Board Regular dbwiz's Avatar
    Join Date
    Nov 2007
    Location
    California
    Posts
    275

    Default Re: how to put a comma between last name and first name but not first name and MI

    Wow, that's awesome!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com