Text to Columns problem

[Bigmal70]

I have a problem with text to columns with data downloaded from our website. The address field is coming through with the whole address in a single cell with multiple rows.

I have tried the following but all I get is the first row in the first colimn and the rest of the data:

Text to Columns, Delimited, Other and ALT-0010
Text to Columns, Delimited, Other and ALT-010
Text to Columns, Delimited, Other and ctrl-j

I have even created a macro to replace the hard returns with commas and then use comma as a delimiter but that still does the same.

I have attached a file with two of the records (1 tab for the original data and one with the commas) and would appreciate it if someone could point out what I am missing here.

Finally, I have tried this in both 2003 & 2010 and get the same issue in both.

[martindwilson]

those returns are a mixture of char(13) and char(10)

[Bigmal70]

Thanks, but that still does not seem to work. Could it be that there are other characters there as well and if so how would I be able to find and identify these?

[martindwilson]

well this seems to work
=SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),","),CHAR(13),",")
fill down
copy paste back as special values
then use text to columns delimited choose comma , choose treat consecutive delimiters as one