Solved: Cleaning Cells with inverted Name and Surname

[Slicemahn]

Hello Everyone

I am trying to purge excess info within cells. I have written a macro that gives me a 400 error each time.

There are about 30,000 rows in which I have to clean. The content of each cell is the following:

SPRATT, JACK - 12345 (35972)

My goal is to have

JACK.SPRATT 35972 preferably with the name in one column and the ID in another.

Here is the code I wrote that returns the error

[VBA]Sub DeleteExcess()
Dim EndRow As Integer
Dim RowLoop As Integer
Dim StringChecker As Integer
Dim NewString As String
Dim FindDash As Integer

EndRow = Cells(65536, 1).End(xlUp).Row
For RowLoop = EndRow To 1 Step -1
FindDash = Application.WorksheetFunction.Find("-", Cells(RowLoop, 1))
StringChecker = Len(Cells(RowLoop, 1))
NewString = Left(Cells(RowLoop, 1), (StringChecker - FindDash))
Cells(RowLoop, 1).Value = NewString
Next RowLoop
End Sub
[/VBA]

I look forward to any thoughts or suggestions on how to conquer this problems. Many thanks.

[unmarkedhelicopter]

Sub ReorderNameUMH()
    Dim lnRw As Long, lnNS As Long, lnNE As Long, sName As String
    Dim lnIDS As Long, lnIDE As Long, sID As String, lnID As Long
    For lnRw = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
     lnNS = InStr(1, Cells(lnRw, 1).Value, ", ")
     lnNE = InStr(lnNS, Cells(lnRw, 1).Value, " - ")
     lnIDS = InStr(lnNE, Cells(lnRw, 1).Value, "(") + 1
     lnIDE = InStr(lnIDS, Cells(lnRw, 1).Value, ")")
     If lnNS * lnNE * lnIDS * lnIDE = 0 Then
       Cells(lnRw, 2).Value = "Error in Input"
      Else
       If IsNumeric(1) Then
         Cells(lnRw, 2).Value = Mid(Cells(lnRw, 1).Value, lnIDS, lnIDE - lnIDS)
         Cells(lnRw, 1).Value = Mid(Cells(lnRw, 1).Value, lnNS + 2, lnNE - lnNS - 2) & _
         "." & Left(Cells(lnRw, 1).Value, lnNS - 1)
        Else
         Cells(lnRw, 2).Value = "Error in Input"
       End If
     End If
    Next lnRw
End Sub

Don't use integers, don't use worksheet functions where a VBA equivalent exists.

I don't like this, I think name SHOULD be stored Surname first, so I'd maybe go for three fields : ID, Surname, Firstname

It's a bit clunky so you can see where the numbers are used, you could trim the code after if you are confident.

[Bob Phillips]

[vba]

Sub DeleteExcess()
Dim EndRow As Integer
Dim RowLoop As Integer
Dim StringChecker As Integer
Dim FindDash As Integer
Dim mpComma As Long
Dim mpDash As Long
Dim mpBracket As Long
Dim mpId As String

With ActiveSheet
EndRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For RowLoop = EndRow To 1 Step -1
With .Cells(RowLoop, "A")
mpDash = InStr(.Value, "-")
mpComma = InStr(.Value, ",")
mpBracket = InStr(.Value, "(")
StringChecker = Len(.Value)
End With
mpId = Mid(.Cells(RowLoop, "A").Value, _
mpBracket + 1, StringChecker - mpBracket - 1)
With .Cells(RowLoop, "A")
.Value = Mid(.Value, mpComma + 2, mpDash - mpComma - 3) & _
"." & Left(.Value, mpComma - 1)
End With
.Cells(RowLoop, "B").Value = mpId
Next RowLoop
End With
End Sub
[/vba]

[unmarkedhelicopter]

And ...
Some error checking might be nice !
Sometimes ...

[Slicemahn]

Both solutions work well. I am little confused with xld's code.