D on YouTube asks: How do I stop Excel from removing leading zeros when opening a file?
Changing a column from general to text does not work for me, as the “0” has already been removed.
09805 becomes 9805, for example.
The files I’m opening does not make it clear that the zero has been removed.
I'm working in spreadsheets with 200k rows.
So stopping Excel from removing them in the first place, would be preferable
Changing a column from general to text does not work for me, as the “0” has already been removed.
09805 becomes 9805, for example.
The files I’m opening does not make it clear that the zero has been removed.
I'm working in spreadsheets with 200k rows.
So stopping Excel from removing them in the first place, would be preferable
Transcript of the video:
How do we open a CSV file in Excel without losing the leading zeros?
This question just came in from D on YouTube.
Has 200k rows. When he opens it, the 09805 becomes 9805.
So I'm just doing this little test here with Test.CSV.
Three different methods I'm going to talk about. First method, just double click.
And you see, we lose leading zeros.
But on the latest version of Microsoft 365 Insider Fast, we have this option to Don't Convert and it will keep the leading zeros.
But most people don't have that. So that's not an option in most cases.
Next up, we're going to go to Data.
Get Data, From a File, from Text/CSV, which I guess is the same as clicking that. We're going to find the CSV, click Import.
Click Transform. The damage is already done.
The zeros are gone, but right here we uncheck Change Type and the zeros come back.
Close and load, and we're good to go. Now that's Power Query.
The hassle with Power Query is, it's not going to work on a Mac.
So, File, Options, Data, Show Legacy data import wizards From Text.
Click Okay.
Then, Data, Get Data, Legacy Wizard, From Text, click on Text.CSV, and it forces us into the Text Import Wizard, which is better. So delimited by a comma.
And then in step three, that gives us a chance here to change the General to Text.
Click Finish. Boy, where are we going to go?
To a New worksheet. Click Okay.
All right. And that keeps the leading zeros.
Three different ways to solve the problem.
That new way, where they just ask you, hey, do you want to knock that, get rid of these leading zeros?
Once that rolls out to the world, that'll be great.
But right now, it's just a little bit too new, and one of those other two methods.
This weekend still have Wyn's book, Power BI For the Data Excel Analyst, just shipping the United States.
Click that eye on the top right hand corner.
We've shipped a lot of these, but there's still some left.
This is your last chance to get it before the big release on November 1st.
Well hey, I want to thank D for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another Net Cast from MrExcel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the Comments below.
This question just came in from D on YouTube.
Has 200k rows. When he opens it, the 09805 becomes 9805.
So I'm just doing this little test here with Test.CSV.
Three different methods I'm going to talk about. First method, just double click.
And you see, we lose leading zeros.
But on the latest version of Microsoft 365 Insider Fast, we have this option to Don't Convert and it will keep the leading zeros.
But most people don't have that. So that's not an option in most cases.
Next up, we're going to go to Data.
Get Data, From a File, from Text/CSV, which I guess is the same as clicking that. We're going to find the CSV, click Import.
Click Transform. The damage is already done.
The zeros are gone, but right here we uncheck Change Type and the zeros come back.
Close and load, and we're good to go. Now that's Power Query.
The hassle with Power Query is, it's not going to work on a Mac.
So, File, Options, Data, Show Legacy data import wizards From Text.
Click Okay.
Then, Data, Get Data, Legacy Wizard, From Text, click on Text.CSV, and it forces us into the Text Import Wizard, which is better. So delimited by a comma.
And then in step three, that gives us a chance here to change the General to Text.
Click Finish. Boy, where are we going to go?
To a New worksheet. Click Okay.
All right. And that keeps the leading zeros.
Three different ways to solve the problem.
That new way, where they just ask you, hey, do you want to knock that, get rid of these leading zeros?
Once that rolls out to the world, that'll be great.
But right now, it's just a little bit too new, and one of those other two methods.
This weekend still have Wyn's book, Power BI For the Data Excel Analyst, just shipping the United States.
Click that eye on the top right hand corner.
We've shipped a lot of these, but there's still some left.
This is your last chance to get it before the big release on November 1st.
Well hey, I want to thank D for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another Net Cast from MrExcel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the Comments below.
