Need to keep leading zero's when saving to .CSV in Microsoft 365 MSO

Question

I am using Microsoft Excel for Microsoft 365 MSO and have a list of part numbers that are numbers stored as text with leading zeros.  When I save as .CSV for file import, the leading zeros disappear.  I need the .CSV file to retain the leading zeros.  I have tried reformatting as text with no success.  I have tried using the Convert Text to Columns Wizard with no success. I tried formula =LEFT(A2,LEN(A2)) and it worked until I need to copy and paste values, then it pastes it as Number stored as Text. Please help me figure this out.

Answer 1

This will work with copy/paste. When pasting, select Paste Special:

Select Values and Add:

This will paste the text values as numeric values.

Kevin

Answer 2

Try a macro to retain the formatting of the cell as a TXT file rather than a CSV: When you open the file, you will be able to use the text file dialog to specify that column being pulled in as text (which should preserve the leading 0s), which you can't do with a CSV file.  Of course, if you are not using Excel to re-open the file, change the .txt extension to .csv and the program you are using may keep the leading 0s.

Sub ExportCurrentSheetToTXT()

    Dim fName As String

    Dim WholeLine As String

    Dim FNum As Integer

    Dim RowNdx As Long

    Dim ColNdx As Long

    Dim StartRow As Long

    Dim EndRow As Long

    Dim StartCol As Long

    Dim EndCol As Long

    fName = "C:\Excel\ExportText.txt"  'use a folder that exists

    Application.ScreenUpdating = False

    On Error GoTo EndMacro:

    FNum = FreeFile

    With ActiveSheet.UsedRange

        StartRow = .Cells(1).Row

        StartCol = .Cells(1).Column

        EndRow = .Cells(.Cells.Count).Row

        EndCol = .Cells(.Cells.Count).Column

    End With

    Open fName For Output Access Write As #FNum

    For RowNdx = StartRow To EndRow

        WholeLine = ""

        For ColNdx = StartCol To EndCol

            If WholeLine = "" Then

                WholeLine = Cells(RowNdx, ColNdx).Text

            Else

                WholeLine = WholeLine & "," & Cells(RowNdx, ColNdx).Text

            End If

        Next ColNdx

        Print #FNum, WholeLine

    Next RowNdx

    Close #FNum

EndMacro:

    On Error GoTo 0

    Application.ScreenUpdating = True

End Sub

Answer 3

Thank you for the response. It does, but it removes the leading zeros, which I'm needing to keep. And if there is data set that is not a number formatted as text, it does not paste. So I don't think this is the solution to my problem.

Answer 4

You stated two problems in your original post. The first was exporting to a CSV while keeping leading zeroes. You stated you had a solution for that using a formula. The next problem you stated was "I tried formula =LEFT(A2,LEN(A2)) and it worked until I need to copy and paste values, then it pastes it as Number stored as Text." My solution solves that by converting the text values to numeric values. If you want the leading zeroes then you don't have any problems at this time. If you want the numbers stored as numbers then use my solution for pasting.

If you want numbers stored as numbers to have leading zeroes on the worksheet then you have to format those cells appropriately. Here is an example of a number with six positions, five of them are leading zeroes:

Kevin

Answer 5

You can also solve the first problem using formatting. When numbers are formatted as illustrated in my previous post, saving the worksheet as a CSV file preserves the formatting.

Kevin