Hi Richard,

Just continuing where I left off and what I was able to figure out, but still need further guidance on how to extract text data.

I have tried using the (instr function)...as you have suggested but still finding it difficult to line-up the text into one row.

Function in Query:

LastStr: InStr(50,[Field1],"1") which locates the last (1) in the row
CarrierName: Mid([Field1],[LastStr]+1,100) which pulls out the carrier names.

The above (instr function)...where I was able to pull out the desired data gives me the below result.

Field1 LastStr CarrierName
05:00    74033          3169457      1     1     1 50
BORDER EXPRESS     KIMBERLY CLARK AUSTRALIA 0 BORDER EXPRESS     KIMBERLY CLARK AUSTRALIA
06:00    74034          3169457      1     1     1 50
SCT LOGISTICS     COLGATE PALMOLIVE 0 SCT LOGISTICS     COLGATE PALMOLIVE


Now I can't seem to figure out how to line this up into one row of text as an example below is what I'm looking to achieve.


Field1 LastStr CarrierName
05:00    74033          3169457      1     1     1 50 BORDER EXPRESS     KIMBERLY CLARK AUSTRALIA
BORDER EXPRESS     KIMBERLY CLARK AUSTRALIA 0
06:00    74034          3169457      1     1     1 50 SCT LOGISTICS     COLGATE PALMOLIVE
SCT LOGISTICS     COLGATE PALMOLIVE 0


Your expert advice is highly appreciated.

Regards

Hamish

---

Reply from Richard Rost:

It looks like you just need to remove a NewLine character from in there and it's exactly the way you want it. Try:

MyString = Replace(MyString,vbNewLine,"")

This will replace the NewLine character with a blank string.