Need to Clean up my excel file with many columns of text string without losing data

[bmjones1013]

The attached excel file has line delimiters in several columns of string text. The first row is the headers row, which I've been able to use the text to columns function on the data tab and it breaks them into columns. However, when I try to do the same for the other rows, I lose data. Is there any other way to do this and preferably quicker than one column or cell at a time.

[CK76]

Is there source file? Looks like the file in question had already some data transformation applied and is not in it's raw format.

Having raw file would help in giving you the best fit solution.

Edit: Otherwise, you'll need to re-concatenate contents of each row into single cell and then split them apart.

[Richard Buttrey]

Hi and welcome to the forum.

Did the original file, before you performed a Text to columns on the header row, have delimited strings in various columns? And as an aside row 1684 looks a bit suspect with the oddball characters starting in column L.

I had originally thought that the simplest way given what you present would be to concatenate columns A:Y (Y being the last column with text ignoring row 1684) into a single column and then perform a Text to Columns on that column.

Are you not able to get the file with all the data in a single column. Most back office systems which export data have a facility to specify a csv or even Excel format output file. Have you asked?