text to column

[CAAT]

Hi, I'd like to separate a record into columns
I have included an example in the attached spreadsheet.
The problem records are in YELLOW and the solution that I want is highlighted in GREEN

PLease assist

Thanks in advance.

 

[Hui]

Caat

Using a few formulas:
D3: =VALUE(LEFT(B3,10))
E3: =MID(B3,LEN(D3)+1,LEN(B3)-LEN(D3)-LEN(F3)-LEN(G3)-1)
F3: =RIGHT(LEFT(B3,LEN(B3)-LEN(G3)-1),LEN(B3)-FIND("|",SUBSTITUTE(B3," ","|", LEN(B3)-LEN(SUBSTITUTE(B3," ","")))))
G3: =RIGHT(B3,LEN(B3)-FIND("|",SUBSTITUTE(B3," ","|", LEN(B3)-LEN(SUBSTITUTE(B3," ","")))))

Copy D3:G3 down

 

[CAAT]

hi

phew this is complex. i was hoping for a simpler formula

thanks though

 

[Hui]

If your data is in a standard format like columns or had commas to separate fields it would be a lot easier
Are you able to have the data exported as comma delimitered?