Option Explicit
Sub ConvertWorksheet()
Dim varLineData As Variant
Dim lX As Long
Range("B1").Resize(1, 7).Value = Array("Emp ID", "Name", "Posn Order", "Department", "Position", "Old rate", "New Rate")
For lX = 2 To Cells(Rows.Count, 1).End(xlUp).Row
varLineData = ConvertOneRow(Cells(lX, 1).Value)
Cells(lX, 2).Resize(1, UBound(varLineData) + 1).Value = varLineData
Next
End Sub
Function ConvertOneRow(sInput As String)
Dim sID As String
Dim sName As String
Dim sPosOrder As String
Dim sDept As String
Dim sPosition As String
Dim curOldRate As Currency
Dim curNewRate As Currency
Dim lDashPos As Long
Dim lOpenParaPos As Long
Dim lCloseParaPos As Long
Dim lSpacePos As Long
Dim lTextPos As Long
Dim lTextLen As Long
sInput = Trim(sInput)
lSpacePos = InStr(sInput, " ")
sID = Left(sInput, lSpacePos - 1)
sInput = Mid(sInput, lSpacePos + 3)
lOpenParaPos = InStr(sInput, "(")
sName = Trim(Left(sInput, lOpenParaPos - 1))
lCloseParaPos = InStr(sInput, ")")
sInput = Trim(Mid(sInput, lCloseParaPos + 1))
lSpacePos = InStr(sInput, " ")
sPosOrder = Left(sInput, lSpacePos - 1)
sInput = Mid(sInput, lSpacePos + 1)
lDashPos = InStr(12, sInput, "-")
sDept = Trim(Left(sInput, lDashPos - 10))
lTextLen = Len(sDept)
sInput = Trim(Mid(sInput, lTextLen + 2))
lSpacePos = InStrRev(sInput, " ")
curNewRate = Trim(Mid(sInput, lSpacePos + 1))
sInput = Trim(Left(sInput, lSpacePos - 1))
lSpacePos = InStrRev(sInput, " ")
curOldRate = Trim(Mid(sInput, lSpacePos + 1))
sPosition = Trim(Left(sInput, lSpacePos - 1))
Convert = Array(sID, sName, sPosOrder, sDept, sPosition, curOldRate, curNewRate)
End Function