[William Lisowski]

I need to use the identifier to match other information.

And does that other information also have positions 16-18 of the identifier replaced by zeroes when all three characters are digits?

Added in edit:

Perhaps you will find the following helpful.

=IF(ISNUMBER(A2),VALUETOTEXT(A2/1000),LEFT(A2,15))

when pasted into cells B2:B67 in your example workbook, produces a string containing the first 15 digits, which import excel brings into Stata as a 15-character string.

[Leonardo Guizzetti]

If you wish to edit in Excel beforehand, just select the column; right-click the mouse; select format cells; finally, choose the category you want to select.

This is where Excel is more helpful than it should be, and will reformat the IDs that can be interpreted as numbers as such, and format them in scientific notation despite explicitly tell Excel to treat it like a string.

[Leonardo Guizzetti]

You will need to make a change to the Excel file at some point, either to fiddle with cell formatting or otherwise suggestion already mentioned. Perhaps a compromise here is to create a new column in your Excel file with a formula that adds some letter as prefix to the IDs, which was the suggestion made by Nick in #4. Call it AID or something.

=CONCAT("A", A2)

Then you can import this new column and not the old ID, while still maintaining the integrity of the original ID in the Excel source file. In Stata, you can strip the prefix:

gen id = substr(a, 2, .)  // will be a string

If you later need a numeric value for an identifier, you can generate one using -egen, group()- which will be your "pseudoid", but do be sure to bring along the string -id- with it.

[Fred Lee]

And does that other information also have positions 16-18 of the identifier replaced by zeroes when all three characters are digits?

Yes, It does.

[Fred Lee]

You will need to make a change to the Excel file at some point, either to fiddle with cell formatting or otherwise suggestion already mentioned. Perhaps a compromise here is to create a new column in your Excel file with a formula that adds some letter as prefix to the IDs, which was the suggestion made by Nick in #4. Call it AID or something.

Yes, Thank you! Editing the excel file is the compromise way that can solve the problem. Maybe we cannot find a better solution.