splitting multile lines in one cell into columns

[m4rty5miff]

I have a database with 8000 erecords. Some columns have cells with multiple lines of text, it looks alike a alt+enter carriage return, but there is no 'charachter' for the carriage return. I want to seperate the mulitple lines into unique columns, but can't figure out how to do it.

I have attached a single line of the database in a zip file - I really only need to separate column 'M' these are contact names and I want to have each name in a separate column.

Can anyone help, very urgent, will pay small fee if someone can solve this?
Thanks in advance.

Martin Smith

[smuzoen]

The contact names appear to be in column K - you can split these type of entries with Chr(10)

Please Login or Register  to view this content.

See attached workbook

[MatrixMan]

Only other comment here would be that it sometimes depends on how the data is entered in the sheet in the first place; if it is being entered directly then using the split function as Anthony has shown will work fine. But if your data is imported, added via code or otherwise manipulated it can sometimes also have chr(13) characters in it. You could put in another check for chr(13) as shown or you could "manually" check for both like this:

Please Login or Register  to view this content.

The above is doing "manually" what the Split function does and checks for both chr(10) and chr(13). To run: paste it into the sheet with your data, select the column you want to split out and it will write the data to the next available column in that row (which means you can select any column and split out whatever is in multiple lines across your sheet).

Hope that helps.