Cell to multiple text column splitting

[RAM72]

Hi ALL

Annexed file I have a master sheet with codes description origin abreviations and total, however see the results text to columns sheet as per annexed file, see the needed results sheet I required, is there a formula to have code , description, origin and total in their respective columns as it is very tedious to this manually The Complexity of the problem is description have 2, 4, 5 spaces making it difficult check text to columns sheet


Help help please Many thanks in advance .
Best regards

 

[Deepak]

Pls Check this...

Option Explicit

Sub Split_Text()
Dim i As Long, Str As String
[B1:D1] = Array("CODE", "DESCRIPTION", "ORIGIN")
For i = 2 To 5
Str = Replace(Cells(i, 1).Value, " Total", "")
Cells(i, 2) = Left(Str, 10)
Cells(i, 3) = Mid(Str, 13, Len(Str) - 16)
Cells(i, 4) = Right(Str, 2)
Next
End Sub

 

[shrivallabha]

With your first sheet sample data following formulas work:
B2 : =LEFT(A2,10)
C2 : =MID(LEFT(A2,SEARCH("total",A2)-4),13,99)
D2 : =SUBSTITUTE(RIGHT(A2,8)," Total","")
copy down