Text to columns problem - data disappearing

[DJDALSH]

Hi there,

I am trying to separate 160 rows of data into columns. Each piece of data is separated by a | currently, however between clicking next after step 1 and step 2 the majority of the data disappears and when I click finish only 2 pieces of data from the cell have separated and the rest has disappeared.

Does anyone know why this is happening? An example of the data in the cell I'm trying to separate is:

Contact name * xxxxx|Organisation name * xxx|Number of employees * xxx|Phone Number * xxx |Email * xxx@xx.com <mailto:lxxx>|Address * <http://maps.google.com/?q=xx, +xxxxxx++xxx+xxx> |xxx,xxx,xxx |United Kingdom |Preferred date of delivery if one Monday 1 September 2014 |If you would like additional copies of 'xxx' for your organisation. Please provide further details for how these will be used below.|

Thanks very much,
Dan

 

[ProfessorPorcupine]

Seems to work for me, try using Text To Columns command on your Data section of the ribbon, pick Delimited, then uncheck Tab and check Other as the delimiter and copy and paste the | symbol in the related Other box.

 

[Asala42]

Drag your formula bar so you can see multiple lines at once and click around on where you thought the data would show up - a possibility is the delimitation is working properly but cells containing carriage returns would force the data several lines down within a cell. It would give the appearance of missing data at a glance if your formula bar is only displaying a single row height (a common default).