I have to take a column of unique 7 digit codes, some of which contain multiple leading zeros, which are listed in each row and separated only by semicolons. I need to expand this column into multiple columns, so each code has its own cell.

I used Text to Columns to achieve this, and it seemed to work. BUT, my data set is very large, some cells contain 200 codes, and there are almost 1000 rows. Text to Columns is not allowing me to assign Text formatting to ALL output columns, so leading zeros are being removed. I can select the first column, and sometimes the horizontal slide bar in the wizard allows me to select the first couple, but generally, it won't let me go further. I have an entire team stumped by this. Due to the nature of our work, we cannot use macros and we only have Excel 2016. I have tried many workarounds, and nothing seems to keep Excel from deleting our leading zeros.

Any advice is appreciated.