Problem with the data form a multi-line Excel cell

[Navid Asgari]

Dear community,

I loaded an Excel file where two columns contain more than one line (The cell includes, for example, industry classification of firms--firms can have more than one industry code. The database that I use puts each code in one line in the Excel sheet). When I import the Excel file to Stata something weird happens:

-The value of the cells under that column appears in one line and the carriage returns seem to appear as spaces at the end of the string (when I -br- the data). I tried using the dataex to show you the data. But, dataex does not capture the problem. So, here is how I see the data:

The first line is fine. The second line is supposed to contain these industry codes:
524210
524126
524128
524130

When I count the number of characters for this line, Stata tells me that it has 27 characters (6*4 +3)

But, then I -ltrim- (or rtrim) the variable, there seems to be no space at the end. When I try to list and see the value, I see this:

Here it seems that carriage returns appear as spaces between the 6-digit codes. But, when I try to split this based on spaces, nothing is split.

So, the issue is that the spaces (produced through space carriages in Excel) seem to be at the end of the string when I browse the data. When I list the data, they seem to be between 6-digit codes.

What is going on here? How can I split these industry codes into different cells or put a punctuation mark between them?

Thanks,
Navid

[Jared Greathouse]

Is the excel file publically avaliable? That is, is the excel file easily importable, like say

import exc "http://www.demoscope.ru/weekly/app/app4087.xls", clear

EDIT: The reason I ask is because for questions like this that involve importing stuff and so on, having access to the real dataset if it's public can be helpful.

[Navid Asgari]

Hi Jared,

Here it is: https://file.io/yIUt1UdZOrFY

Good idea!

Thanks,
Navid

[Jared Greathouse]

It says the transfer file requested has been deleted. What's the website this is coming from? Are you sure it's publically free to those who want it?