swapping data round in excel

[PhilipLawrence99]

Hi there, Football related question this time.... I have a list of all the players in the preier league that I need to import into a program to monitor the league for 2002-2003 but the list needs to be in a certain format. My list is set out as follows:
ColumnA = Player No.
ColumnB = Player Name (Forename Surname)
ColumnC = Position

What I need to do is transpose the forename and surname in columnB so it reads Surname,Forename with a comma separating the surname and forename but still in the same cell.

any thoughts on how to do this as there's over 500 to type in manually otherwise....

If you want the program to use then go to http://www.ngthomas.co.uk/index.html

Look forward to hearing from you guys soon.

cheers

 

[ttmac]

The following macro should do the trick:-

Option Explicit

Sub Name_Order()
Dim rng As Range
Dim cell As Object

Set rng = Range("C2:C4")

For Each cell In rng
If Not (IsEmpty(cell) Or (cell = "") Or (IsNull(cell))) Then
Dim myArray() As String
myArray() = Split(cell, " ")
cell.Value = myArray(1) & ", " & myArray(0)
End If
Next cell

End Sub

This is based on a firstame and lastname and does not cater for multiple names

 

[PhilipLawrence99]

That's great, thank you for that but there's loads of double barrelled surnames in the league and I'll need to cater for those as well. any thoughts???

Cheers,

 

[ttmac]

When you say double barrelled - are these hyphenated or seperate words as in alf inge halaand, or whatever his name is?

The code could be amended such that alf inge halaand would become halaand, alf inge and firstname sur-name would become surname, firstname.

Tom.

 

[ttmac]

Try,

Option Explicit

Sub Name_Order()

Dim x As Integer
Dim rng As Range
Dim cell As Object
Dim newname As String

Set rng = Range("C1:C10")

For Each cell In rng
If Not (IsEmpty(cell) Or (cell = "") Or (IsNull(cell))) Then
Dim myArray() As String
myArray() = Split(cell, " ")
newname = myArray(UBound(myArray)) & ", "
For x = 0 To (UBound(myArray) - 1)
newname = newname & " " & myArray(x)
Next x
cell.Value = newname
End If
Next cell

End Sub

 

[PhilipLawrence99]

They're separate names with no separator between them. Cheers,

Phil