Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Hemel and/or Luton, UK, Hertfordshire, England
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Functions (XP SP-2)

    Folks,

    I have a spreadsheet that is sent to me monthly by one of my suppliers and it lists (amongst other things) the names of several hundred staff members. The names are all listed as FIRSTNAME SURNAME (so I would be Jon Roberts) but I need the names reversed to SURNAME FIRSTNAME (ie Roberts Jon).

    Currently I make a new column, insert the formula below and then copy it down the column.

    =RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1))

    Finally I have to copy my whole new column and Paste Special - Values it over itself to fix these new reversed names before I delete the originals.

    What I really want is one of those clever VB thingies that I think are referred to as Custom Functions. I want to be able type =NameReverse(A1) and it would just return the switched name. Then I would just have to do the Paste Special bit and I would be done. Obviously a bit of code that sorted the Paste Special part as well would be even better but I don't think that could be part of a Custom Function.

    Thanks for your thoughts.

    Jon Roberts

  2. #2
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Functions (XP SP-2)

    ok Roberts Jon,
    I'm sure you'll get some clever stuff for doing exactly this, but I belong to the 'keep it simple' tendency. You could consider using the text to columns thing to split the names by fiinding the space, then doing the inversion and pasting special that way.
    The macro recorder will faithfully watch and record this as you do it. The only improvement I'd make to the code generated is to add a new first line
    Application.screenupdating=false
    Suggest then attach it to a button or a toolbar.
    One thing I've found handy when accepting other people's lists is the facility by using the =proper function to turm JON ROBERTS into John Roberts, if you wanted to.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Functions (XP SP-2)

    <P ID="edit" class=small>(Edited by sdckapr on 09-May-03 10:44. OOPS, forgot the sorting bit)</P>This is not a FUNCTION, but it does everything you want to do. select the range with the names ("first last"), and all the names will be reversed ("last, first") by running the macro.

    Steve
    <pre>Option Explicit
    Sub ReverseName()
    Dim rCell As Range
    Dim iSpace As Integer
    Dim sName As String
    Dim sFirst As String
    Dim sLast As String

    For Each rCell In Selection
    sName = rCell.Value
    iSpace = InStr(sName, " ")
    sLast = Right(sName, Len(sName) - iSpace)
    sFirst = Left(sName, iSpace - 1)
    rCell.Value = sLast & ", " & sFirst
    Next rCell

    Selection.CurrentRegion.Sort _
    Key1:=Range("A1"), Order1:=xlAscending

    End Sub
    </pre>


  4. #4
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts

    Re: Custom Functions (XP SP-2)

    This is so great! I very much appreciate your suggestion about using text-to-columns; I had never thought of that.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Custom Functions (XP SP-2)

    Hi Steve,

    A great and simple approach to a problem I had previously always addressed with formulae!

    However I had a problem when I tried your sort given that cell A1 was not in my current region. Can you suggest a variation that will that will work regardless of the location of the current region? Maybe the column to be sorted?

    Regards,

    Peter Moran
    Two heads are always better than one!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Functions (XP SP-2)

    Change the last instruction to

    Selection.CurrentRegion.Sort _
    Key1:=Selection.CurrentRegion.Range("A1"), Order1:=xlAscending

    This specifies that the upper left corner of the current region is the sort key.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Custom Functions (XP SP-2)

    Hans,

    Thanks, Works a treat!

    Regards,

    Peter Moran
    Two heads are always better than one!

Posting Permissions

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