Barcodes in Exel

HShowalt If all you need is to save EAN numbers that you already have without errors in Excel - go with the spoiler and save them as text.

If you need to import and work with numbers generated outside the workbook, spend some time to understand the difference between numbers and strings. The replies above contains both information and possibilities but it’s up to you to balance the reading effort against your needs.

I am having this exact same issue with a different POS provider. Have tried absolutely everything, but every time I try to upload the csv spreadsheet to my website provider, the product code data column converts automatically back to the scientific code. Anyone figure out any lasting solutions to this issue?Rocky247 

Rocky247 Since conversion to scientific data may truncate accuracy already in the systems output, you will depend on having a correct combination of product code and bar code outside the host system.

Merging with a previous list where you already have fixed the bar codes manually, you will in the future only have to fix the new products;

Column O may miss accuracy for the last digits or be in scientific format if using Data: From Text/CSV as suggested by Riny_van_Eekelen.

Column P is looking up the previous bar code for known products.

=LET(
  prodCode;  OFFSET(A6;0;0;COUNTA(A:A)-1;1);
  barCode;   OFFSET(prodCode;0;COLUMNS(A:O)-1);
  prevOutput;XLOOKUP(prodCode;prevReturn2dear!A:A;prevReturn2dear!O:O);
  output;    IFS(
               ISNA(prevOutput);barCode;
               prevOutput=barCode;prevOutput;
               1;prevOutput
             );
  comment;   IFS(
               ISNA(prevOutput);"new product";
               prevOutput=barCode;"-";
               1;"using previous bar code"
             );
  output
)

Attached also as an xlsX without macros that should be okay if you don't get problems (like conversions to scientific) when saving as csv.

Hi Rocky247 

> it shows correctly in the system but when exported (as a csv - thats the only option) it is a scientific number as per your screenshot

This sounds like DEAR Systems code generates the output like it was created by Excel instead of writing the raw data directly to a csv file.
Ev figures omitted in rounding to scientific before reaching the csv file are lost while trailing zeros may be rescued.

__/ reading macro

I added another test line with a leading zero

and can via a macro read them as text

Row 26. An inserted leading apostrophe (') makes the cells content commented and not interpreted as a number.

Row 24. Converts scientific format to plain numeric (0). Here, trailing zeros are appended.

Sub CSV_Read()
    path = ThisWorkbook.path & "\" & Range("source").Value
    foundFile = Dir(path)
    If foundFile <> "" Then
        Range("target").CurrentRegion.ClearContents
        
        Close
        fileNo = FreeFile
        
        Open ThisWorkbook.path & "\" & foundFile For Input As #fileNo
        text = Input$(LOF(1), 1)
        Close #fileNo
        
        allRowsInArray = Split(text, vbCrLf)
        rowCount = UBound(allRowsInArray)
    
        rowCounter = 0
        Do While rowCounter < rowCount
            currentLineToArray = Split(allRowsInArray(rowCounter), ",")
            columnCount = UBound(currentLineToArray)
            For columnCounter = 0 To columnCount
                If columnCounter = Range("special_columnNo").Value Then
                    If InStr(currentLineToArray(columnCounter), "E") > 0 Then
                        Range("target").Offset(rowCounter, columnCounter).Value = "'" & Format(currentLineToArray(columnCounter), "0")
                    Else
                        Range("target").Offset(rowCounter, columnCounter).Value = "'" & currentLineToArray(columnCounter)
                    End If
                Else
                    Range("target").Offset(rowCounter, columnCounter).Value = currentLineToArray(columnCounter)
                End If
            Next
            rowCounter = rowCounter + 1
        Loop
    End If
End Sub

__/ writing macro

Using macro also for saving the edited csv file, the codes still looks like 'text' and the apostrophe is omitted. Leading zero still there.

Sub CSV_Write()
    text = ""
    rowCount = Range("target").CurrentRegion.Rows.Count
    columnCount = Range("target").CurrentRegion.Columns.Count
    
    For rowCounter = 0 To rowCount - 1
        For columnCounter = 0 To columnCount - 1
            If columnCounter = 0 Then
                text = text & Range("target").Offset(rowCounter, columnCounter)
            ElseIf columnCounter = columnCount - 1 Then
                text = text & "," & Range("target").Offset(rowCounter, columnCounter) & vbCrLf
            Else
                text = text & "," & Range("target").Offset(rowCounter, columnCounter)
            End If
        Next
    Next rowCounter
    
    path = Left(Range("source").Value, Len(Range("source").Value) - 4) & "_" & Format(Now(), "YYYY-MM-DD_hhmmss") & Right(Range("source").Value, 4)
    path = ThisWorkbook.path & "\" & Replace(path, "*", "")
    
    fileNo = FreeFile
    Open path For Output As #fileNo
    
    'Write the selected data into the text file.
    Print #fileNo, text      'Write includes "
    Close #fileNo
End Sub

__/ prefs

To avoid editing in the macro, you can enter the input files name in P1. The file is expected to be in the same file as the macro.

P2 gives you the possibility to change column, should the output change column with the bar codes.

Q2 counts the columns offset from first column.

The macro outputs the file starting in the cell named target. In the attached file, A5.

__/ Scientific formatting

When Number Format is "General", the column width affects until 12 figures.

Scientific applies rounding and cuts accuracy.

Rocky247 Then I don't know. Sorry.

Rocky247 Is that file really how it comes out of the POS? When I open it in a text editor it looks like this:

The barcodes are, thus, already included as if they are scientific numbers.

If this is NOT the file from the POS, please upload one of those, without opening and resaving it in Excel first. Use a text editor to remove confidential information and perhaps to reduce the file size.