Sub ParseData()
Dim LR As Long
Application.ScreenUpdating = False
' parse out HR file says data
Rows(1).EntireRow.Insert
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
SC = ActiveCell.Offset(1, 0).Row
Cells(1, 8) = "Website"
Cells(1, 7) = "FAVE SONG"
Cells(1, 6) = "FAVE COLOR"
Cells(1, 5) = "CONTACT"
Cells(1, 4) = "NICKNME"
Cells(1, 3) = "POSITION"
Cells(1, 2) = "CONTACTS"
For i = LR To SC Step -1
MCC = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, ":", "|", 6)) + 2
MCC1 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, ":", "|", 5)) + 2
MCC2 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, ":", "|", 4)) + 2
MCC3 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, ":", "|", 3)) + 2
MCC4 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, ":", "|", 2)) + 2
MCC5 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, "(", "|", 1)) + 1
MCC6 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, ")", "|", 1))
MCC7 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, ":", "|", 1)) + 2
Cells(i, 8) = Mid(Cells(i, 1), MCC, Len(Cells(i, 1)) - MCC + 1)
Cells(i, 7) = Mid(Cells(i, 1), MCC1, MCC - 12 - MCC1)
Cells(i, 6) = Mid(Cells(i, 1), MCC2, MCC1 - 12 - MCC2)
Cells(i, 5) = Mid(Cells(i, 1), MCC3, MCC2 - 14 - MCC3)
Cells(i, 4) = Mid(Cells(i, 1), MCC4, MCC3 - 10 - MCC4)
Cells(i, 3) = Mid(Cells(i, 1), MCC5, MCC6 - MCC5)
Cells(i, 2) = Mid(Cells(i, 1), MCC7, MCC5 - 2 - MCC7)
Next i
MsgBox "Done"
Application.ScreenUpdating = True
End Sub
