Losing (UPC Code) Leading Zeros when Converting to CSV

50yoExcelDummy

New Member
Joined
Jun 12, 2011
Messages
15
The Situation
:
Just before the crash, I output the data from our Point of Sale machine, into a TAB delimited formats, and saved them. Ive now spent over 30 days fighting with this .. I need some help please.

Problem #1:
I keep losing Leading Zeros in my UPC column.

The Solution:
Make the UPC column a TEXT column

PROBLEM #2:
When saving the file with UPC (text column), to a CSV file, I either:
a) Lose my leading zeros, or
b) I end up with a number that almost looks like a "Power", like a "Scientific Notation, for example:
021078013280 (text) results as 21078013280 (no leading zero!),or
021078013280 (text) results as something like 2.31345-11
(as if its magically changed into "Power" or something!)

Thanks!

PROBLEM #3:
One more thing ... There are several files I need to combine, (vlookup) and do a lot more trimming and weeding out, thus, it would be VERY beneficial for me if I could convert my data into a regular XLSX file first.

After I have accomplished all my work, THEN to be able to convert the XLSX file into the final CSV file I need, with the UPC Column being done in TEXT, as to retrain all the leading zeros.

Im sure there was an easier, faster way to say this .. so Ill shut up now..

Thanks again!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
If you are formatted the column as Text, and your UPC codes contain the leading zeroes in them in the Excel sheet, then saving them as a CSV file should not drop the leading zero.

If they appear to be, how are you viewing the CSV file? You should be viewing it in a Text editor (like NotePad). Do NOT use Excel to verify this! If you open a CSV file in Excel, it will drop the leading zeroes upon opening the file in Excel (but the CSV file really does have them!!!).

For this reason, I tell people NEVER to use Excel to verify the format of CSV files. Use a Text Editor.
 

50yoExcelDummy

New Member
Joined
Jun 12, 2011
Messages
15
THANK YOU !

So what you are saying is my work was done 4 weeks ago! ... But by my going in to VERIFY it was right, I messed it up? OMG! I think Im gonna cry! <smile>

According to one of the Mr Excel Videos (which I cant find) I thought I was going to have to find a way to put " " marks around the numbers to lock them in ... when I tried that, I did something wrong and lost the way to do it

I tried something like =(&"(A1)&"), but I couldn't get that to work either .. <sigh> If I would need to do that for this or another part Im working on later, would you know how I can put quotes around the upc numbers for that column (for example)?

(I have over 10,400 items to do! and I sure dont want to have to do them by hand)

Thank You so very much again!</sigh></smile>
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Hi,
Select the range with UPC code and apply this macro:
Rich (BB code):

Sub TestAndFix()
  Dim a, v, r&, c&, rs&, cs&
  With Selection
    .Interior.ColorIndex = 0
    a = .Formula
    rs = UBound(a, 1)
    cs = UBound(a, 2)
    For r = 1 To rs
      For c = 1 To cs
        v = a(r, c)
        If IsNumeric(v) Then
          v = Format(v, "000000000000")
          If Right(v, 4) = "0000" Then
            .Cells(r, c).Interior.ColorIndex = 6
          End If
          a(r, c) = "'" & v
        End If
      Next
    Next
    .Formula = a
  End With
End Sub
If value is not in scientific exponential notation then the leading zeroes will be restored correctly.
For example 21078013280 value converts to 021078013280 string value

Scientific exponential values will be expanded by zeroes too, but cells with 4 right side zeroes will be yellowed for further manual checking & fixing.

Hope this will help a bit
 

50yoExcelDummy

New Member
Joined
Jun 12, 2011
Messages
15
Vladimir Zakharov:

Thank You very much for your reply, however, I have no clue as to even how to use this macro.

Do I use it in on cell, and drag it down (or double click the corner) so that it fills the field down, or do I have to load something else to use it?
Sorry - Im terribly lost.

If I understand this correctly, what you are doing with the macro is ANY string that is shy of having 12 places, it will fill the most left places with zeros?

Because in this case, these people have NO set standard for their SKU number, they for the most part were just using the UPC number, but of the 10,500 products they have ... Id say about 2000 of them are a combination of: Alpha, Numeric, & alpha-numeric. Furthermore, they are anyplace from ONE digit, to 26 char long with spaces, illegal characters, and commas in between!

Will Your macro work for that environment as well?

Sorry to throw you a curve, I was ashamed to come back to you and tell you I had no idea how to use the macro, but, again, Im getting FAT from swallowing all this pride of mine! hehe

Again Thank You!
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
It’s not the cell’s formula but VBA macro code which can be embedded into workbook.

First of all make sure that macro functionality is enabled in your Excel application.
For Excel 2003: use "Tools" menu, point to "Macro", click "Security", choose Medium and press Ok button.
For Excel 2007: click the Microsoft Office Button, click "Excel Options", click "Trust Center", click "Trust Center Settings", click "Macro Settings", choose "Disable all macros with notification", press button "OK" and one more "OK".

How to embed the macro:
1. Select the code of post #4 and copy it into clipboard (use Ctrl-C or do right click & choose "Copy")
2. Open your workbook
3. Hit Alt+F11 to open the Visual Basic Editor (VBE)
4. From the menu choose Insert-Module
5. Paste the code into the appeared code window at right (use Ctrl-V or do right click & choose "Paste")
6. Hit Alt-Q to close the VBE and then save the file
7. Select the range of cells with UPC code in the sheet
8. Hit Alt-F8 and double-click TestAndFix macro

The cells in selection with numeric values will be processed as it was mentioned in post#4.
Neither Alpha nor alpha-numeric values will be modified in selection.

You can also send me the example of your workbook to embed the macro, I will PM you my e-mail for that.
Please replace all sensitive data by dummy.

Regards
 
Last edited:

Forum statistics

Threads
1,082,345
Messages
5,364,817
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top