PDA

View Full Version : Solved: Cleaning Cells with inverted Name and Surname



Slicemahn
12-14-2007, 04:15 AM
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

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


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

unmarkedhelicopter
12-14-2007, 06:05 AM
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 SubDon'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
12-14-2007, 06:08 AM
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

unmarkedhelicopter
12-14-2007, 06:19 AM
And ...
Some error checking might be nice !
Sometimes ...

Slicemahn
12-19-2007, 05:54 AM
Both solutions work well. I am little confused with xld's code.