Losing (UPC Code) Leading Zeros when Converting to CSV

[50yoExcelDummy]

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]

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]

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]

Hi,
Select the range with UPC code and apply this macro:

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]

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]

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

 

[50yoExcelDummy]

wow, Glad I asked.

Sounds easy enough, Ill give it a try and see how it goes!
Thanks!