1
   

Excel csv file, need to protect zeros

 
 
Izzie
 
Reply Wed 25 Nov, 2009 10:36 am
Excel 2007

How to put a barcode starting with zeros into excel and save as a csv (comma delimited) file.

When you enter a number into a cell that begins with zeros because you need that number to be recognised by a barcode reader on an EPoS (electronic point of sale) system, the file needs to be saved as a csv comma delimited file with zeros at the beginning of the barcode

When you save the file as csv file, it appears to save correctly and that the EPOS system will therefore recognise it.

However, when you reopen the csv file to edit, ALL the barcodes automatically are converted to numbers without the zeros in front

i.e. barcode = 0001234567
format the cell as TEXT

have tried to unlock and then protect the worksheet (doesn’t seem to make a difference)

save file as .csv (comma delimited)

http://i277.photobucket.com/albums/kk41/LzzieIzzie/whatever/book1.jpg

click yes (???????)

close excel

open excel

open .csv file

shows 1234567 in the cell

change to 0001234567

save as .csv

upon re-opening, the zeros are always removed.



What I need to happen is to save the excel file as a csv (comma delimited) and open for editing without having to re-enter 1000’s of barcodes that start with zero.

Is this possible (it must be) and how do I do this?

Please, thank you very much.
  • Topic Stats
  • Top Replies
  • Link to this Topic
Type: Question • Score: 1 • Views: 15,567 • Replies: 8
No top replies

 
JPB
 
  2  
Reply Wed 25 Nov, 2009 11:25 am
You can save a comma delimited file with a .txt extension. Then when you open the .txt file it will run you through an import dialogue that allows you to format each column (or any one or more columns). Select the column and format it as "text" and it will open with the leading zeros.

You can also format the file to save text fields with " " around them so that they'll reopen as text but then you have a bunch of ""s in your csv file.
JPB
 
  2  
Reply Wed 25 Nov, 2009 11:29 am
@JPB,
here's the way I actually do it...

save the .csv file

copy it to a .txt file

open the .txt file and edit.
0 Replies
 
OCCOM BILL
 
  0  
Reply Thu 26 Nov, 2009 08:01 pm
@Izzie,
1. Highlight the cell, the column, the row, or any group of cells and then Right Click.
2. Choose "Format Cells"
3. Beneath the Tab titled "Number", choose "Text".
From this point forward, Excel will not recognize anything in these formatted Cells as anything but text, so it will not manipulate your numbers. You still have the option to use Excel's number functions in all other cells not formatted this way.
OCCOM BILL
 
  1  
Reply Thu 26 Nov, 2009 08:47 pm
@OCCOM BILL,
Sorry Izz... missed the part about transfering to CSV... and see now that Excel formatting will not survive the trip. Please ignore my useless advice (and everyone should vote it down.)
0 Replies
 
dadpad
 
  1  
Reply Fri 27 Nov, 2009 12:51 am
Not sure if this is usefull but I can save a column with leading zeros in CSV format by placing a text item in the cell prior to the leading zrero. A full stop, apostrophe, quote mark " or comma.
I was sure ther was a way to format cells for leading zeros but am unable to find it now and i am not sure that this would save leading zeros in CSV format anyhow.
0 Replies
 
dadpad
 
  2  
Reply Fri 27 Nov, 2009 01:07 am
Method 2: Variable-length Numbers
When you want to display leading zeros for variable-length numbers, create a custom format with the same number of zeros (0) (in quotation marks) as leading zeros that you want to display, followed by the number sign (#). For example, if you want to format a variable-length number with three leading zeros, create the following custom number format:
"000"#

To create a custom number format, follow these steps:

1. On the Format menu, click Cells.
2. Click the Number tab.
3. Click Custom.
4. In the Type box, type the custom format.

http://support.microsoft.com/kb/81518
5. Click OK.
xiaoyuandlg
 
  1  
Reply Thu 3 Jul, 2014 03:14 am
@Izzie,
A very good ask, owe to this question, my similar issue about free code to recognize barcode in .net might be corrected. This is a good place to solve problems.
0 Replies
 
crog1967
 
  1  
Reply Thu 18 Jun, 2020 08:12 am
@dadpad,
Worked perfectly! Thank you!
0 Replies
 
 

Related Topics

Clone of Micosoft Office - Question by Advocate
Do You Turn Off Your Computer at Night? - Discussion by Phoenix32890
The "Death" of the Computer Mouse - Discussion by Phoenix32890
Windows 10... - Discussion by Region Philbis
Surface Pro 3: What do you think? - Question by neologist
Windows 8 tips thread - Discussion by Wilso
GOOGLE CHROME - Question by Setanta
.Net and Firefox... - Discussion by gungasnake
Hacking a computer and remote access - Discussion by trying2learn
 
  1. Forums
  2. » Excel csv file, need to protect zeros
Copyright © 2020 MadLab, LLC :: Terms of Service :: Privacy Policy :: Page generated in 0.04 seconds on 07/11/2020 at 01:56:48