5 Replies Latest reply: May 15, 2014 9:54 AM by Arne _ RSS

    Apostrophe when exporting to Excel

    ttom _

      Hello,

       

      I hope someone can help me here, please. I use v.5.02 & Excel 2002.  Everytime I export to excel there is an apostrophe before each number or phrase.  I hope it is a simple fix but it is giving me a lot of trouble doing lookups in Excel.

       

      Please Help!!!  Thanks in advance.

       

       

      Tom

        • Apostrophe when exporting to Excel
          Tom Whiteside

          Tom,

           

          Since both Monarch v5 and v6 are capable of exporting to Excel 8.0 format, the format compatible with the most recent versions of Excel (XP/2002), your default value for .xls file export is probably set too low.

           

          Go into "Options" - "Folders and File Types" - "Export File Types" - "XLS Files,"  and be sure that the setting is for Excel 8.0.  Right now, I would guess it is set to Excel 5.0 or lower.

           

          In the same vein, you should generally set all of your export file types to their highest listed values.  If you have MS Access 2000 or XP/2002, then set the export type to Access 4.0.  However, if you have only Access 95 or Access 97, then set it for Access 3.0.  This setting refers to the MS Jet Database Engine version being used.

           

          Give us another post if your format problem doesn't go away.  Good luck!

           

          Tom

           

          [size="1"][ February 20, 2003, 03:10 PM: Message edited by: Tom Whiteside ][/size]

          • Apostrophe when exporting to Excel
            BryanT _

            I have the same problem - I think the apostrophe is a "non printing character".  It's really annoying as it screws up a lot of follow up work in Excel.  The only solution I've found so far is to export the data as Excel 2.1 format as the apostrophe isn't picked up.  But of course this is a backwards step in other respects.

             

            Anyone got the real solution?

            • Apostrophe when exporting to Excel
              RalphB _

              Check to see if the column of information you are exporting to excel is in numeric format.  If you are exporting it as character format it will put the apostrophe in front of numbers which converts them to text.

              • Apostrophe when exporting to Excel
                ron cousino

                Apostrophes in Excel:

                 

                 

                This is a known issue with the Microsoft Jet Engine. Microsoft has addressed this issue with the following code. If you run this code as a macro within Excel it will remove the hidden apostrophes. Set fields to text format or leading zeros will be lost in fields.

                 

                '  Removes hidden apostrophes as first character

                '  Works on formulas as well as text and values

                 

                Sub RemoveApostrophe()

                Dim CurrentCell

                        For Each CurrentCell In Selection

                                If CurrentCell.HasFormula = False Then

                                    ' Checks to make sure that procedure does not

                                    ' change cell with a formula to be only the value

                                    '

                                    CurrentCell.Formula = CurrentCell.Value

                                    '

                                End If

                        Next

                 

                End Sub

                • Apostrophe when exporting to Excel
                  Arne _

                  Hi, in old EXCEL the apostroph was used for text field's alignment (left, centre, right).

                   

                  Another work around (no macro) is to extract the contents of the field by a formular, then the apostroph is not copied.

                   

                  Ciao, Arne