Excel help

[Sbrem]

I don't really use spreadsheets much, and could use a little help. I created a .csv file from Quickbooks of our customer list to import into Printavo. A rep took our file and opened it up and rearranged it for us and sent it back for us to edit some more. In our geographical location, zip codes all start with a zero. Excel apparently removes those "leading zeroes" by default, so the file I got back has all of the zip codes with only 4 digits, missing the "0" in the front. Searching for an answer, I find that the trick is to format the column as text, so the automatic removal has no effect. OK, that makes sense, so I select that column, and format it as text, then I edited a dozen zip codes, and saved it, which wants to replace the original instead of just updating it, which I don't get, but fine, replace it. When I open it again, the formatting is gone, and so are zeroes I edited in. Heavy sigh...

Steve

[inkman996]

I dont know if this will help since I use Open Office for everything. But when ever I import anything into excel that has leading zeros in columns when presented with import options I can select that column and change it to text. This then imports correctly and always retains it correctly. So maybe you need to start fresh with the import from QB and make sure you change the formatting as desired. Again I do not use excel but I do know the two are nearly exact in processes.

[Sbrem]

That sounds reasonable, but I'm still perplexed about the Save thing not working...

Steve

[aauusa]

this should help figure it out for you  https://www.ablebits.com/office-addins-blog/2017/01/18/add-leading-zeros-excel/#:~:text=Excel%20Number%20Format%20Leading%20Zeros%201%20Select%20a,Click%20OK%20to%20save%20the%20changes.%20See%20More.

[Sbrem]

this should help figure it out for you  https://www.ablebits.com/office-addins-blog/2017/01/18/add-leading-zeros-excel/#:~:text=Excel%20Number%20Format%20Leading%20Zeros%201%20Select%20a,Click%20OK%20to%20save%20the%20changes.%20See%20More.

That is helpful, even if it hurts my head a little. Printavo said they would check it out for me as well. What I don't see in the list is choosing multiple cells to convert from number to text, and add a 0 in front. It's either one cell or the whole column. But I'll test and see what I get. I think I figured out the saving problem, it's a .csv file, it has to be saved as an Excel file.

Steve

[screenxpress]

I think you want to use the "custom" method in the Format Cells.  Be sure and click on (select) the Column to affect all the cells in that column. 

If you want, send me the file and I'll take a look, unless of course, it has any private information.


Update:
Yea, just use 00000 in the "Custom" option for "Format Cells".  I had an old leftover .csv file and checked it out.  Just be sure and select the Column first to get them all with one click.

[zanegun08]

You can't save formatting in a CSV which is why re-opening it isn't saving changes.

Save your formatting to an excel sheet, and then export to a CSV.

Also you may have to add a ' before the number '012312 which may cause an error with importing into printavo but that is how you can also get non standard numbers in a cell

[Sbrem]

I think you want to use the "custom" method in the Format Cells.  Be sure and click on (select) the Column to affect all the cells in that column. 

If you want, send me the file and I'll take a look, unless of course, it has any private information.


Update:
Yea, just use 00000 in the "Custom" option for "Format Cells".  I had an old leftover .csv file and checked it out.  Just be sure and select the Column first to get them all with one click.

That is working for me, thanks. I was dreading typing 1200+ zeroes.


Steve

[screenxpress]

I think you want to use the "custom" method in the Format Cells.  Be sure and click on (select) the Column to affect all the cells in that column. 

If you want, send me the file and I'll take a look, unless of course, it has any private information.


Update:
Yea, just use 00000 in the "Custom" option for "Format Cells".  I had an old leftover .csv file and checked it out.  Just be sure and select the Column first to get them all with one click.

That is working for me, thanks. I was dreading typing 1200+ zeroes.


Steve

BTW, I went back and reread your original post.  Had I read it thoroughly before, I would never have offered for you to send me your customer list file...........DOHHH.  Guess my Icon here is more accurate than I thought, lol.

[Sbrem]

I think you want to use the "custom" method in the Format Cells.  Be sure and click on (select) the Column to affect all the cells in that column. 

If you want, send me the file and I'll take a look, unless of course, it has any private information.


Update:
Yea, just use 00000 in the "Custom" option for "Format Cells".  I had an old leftover .csv file and checked it out.  Just be sure and select the Column first to get them all with one click.

That is working for me, thanks. I was dreading typing 1200+ zeroes.


Steve

BTW, I went back and reread your original post.  Had I read it thoroughly before, I would never have offered for you to send me your customer list file...........DOHHH.  Guess my Icon here is more accurate than I thought, lol.

And I was just about to send it, LOL

Steve