7 Replies Latest reply: May 15, 2014 9:54 AM by RGBrooks _ RSS

    Apostrophe when exporting to Excel 2000

    GrantRoss _

      I am attempting to export a table with 3 columns to Excel 2000.  Column A is a 4 digit number with numeric formatting.  Column B is a 3 digit number such as 001 or 000 or 022.  In order to keep the leading zero's I've formatted the column with character formatting, however, when I export this table to Excel, there is an apostrophe placed in front of the number that is not visible but implies a Text format to Excel.  When I try a Vlookup on that cell, the apostrope is causing the vloopup not to match another text cell without the apostrophe.  I am exporting to the hightest version of Excel in Monarch.  Has anyone figured out a solution to this?  Thank you!

        • Apostrophe when exporting to Excel 2000
          Bob Yarnall

          I’m curious as to why you need the leading zeros?

          If the master lookup range is truly alpha then the apostrophe is needed to make it work.  If the lookup is numeric, then the leading zeros are meaningless.  Is the lookup range simply formatted to show leading zeros but is actually numeric?  This is what it sounds like.  Have you tried exporting the data as numeric and then using the lookup?

          • Apostrophe when exporting to Excel 2000
            GrantRoss _

            The first set of numbers is the account group (xxxx) and the second set of numbers (xxx) is the account sub group.  The master lookup range is truely alpha.  It is defined as alpha via Excel properties, not by using an apostrophe before the value.  I have tried exporting the values as numeric but the master lookup values are character with the leading zero and no apostrophe, so it would not work either.

             

            Originally posted by Bob Yarnall:

            I’m curious as to why you need the leading zeros?

            If the master lookup range is truly alpha then the apostrophe is needed to make it work.  If the lookup is numeric, then the leading zeros are meaningless.  Is the lookup range simply formatted to show leading zeros but is actually numeric?  This is what it sounds like.  Have you tried exporting the data as numeric and then using the lookup? /b[/quote]

            • Apostrophe when exporting to Excel 2000
              Nick Osdale-Popa

              Have you tried changing your formula to something like:

              =VLOOKUP("'"&B1,LookupRange,2,False)?

               

              Using the appropriate info of course.

               

               

              EDIT:

              Curious...I just did a quick test myself and did not seem to have any problems.  I'm using Excel97 and did an export of numbers with leading zeros and they do have the apostrophe in front of them.  Then I formated a cell as text, entered a number, and did a VLOOKUP (without the append) and it worked fine.  How do you have you number set up again for the lookup value?

               

              [size="1"][ March 19, 2003, 01:46 PM: Message edited by: Nick Osdale-Popa ][/size]

              • Apostrophe when exporting to Excel 2000
                Grant Perkins

                Just completed a quick check myself - using Excel 2000 on XP.

                 

                VLOOKUP was fine. If I edit the entries (any combination) and remove or include the leading apostrophe it makes no difference to Excel as far as I can see.

                 

                However I am checking text against text and assume that text against number or vice versa might be problematic. But then I don't think there is a way of formatting leading zeros into the number cell is there? (Maybe there is some deep part of Excel I have never found).

                 

                I'm as puzzled as Nick at the moment.

                 

                Grant

                 

                Originally posted by Nick Osdale-Popa:

                EDIT:

                Curious...I just did a quick test myself and did not seem to have any problems.  I'm using Excel97 and did an export of numbers with leading zeros and they do have the apostrophe in front of them.  Then I formated a cell as text, entered a number, and did a VLOOKUP (without the append) and it worked fine.  How do you have you number set up again for the lookup value? /b[/quote]

                • Apostrophe when exporting to Excel 2000
                  Tom Whiteside

                  Nick's solution seems excellent - - as always - - but in this situation I would be lazy and simply do a global search and replace with your new Excel file.  That is, search for all apostrophes ("'") and replace with (""), effectively stripping them out.  This could be a solution for both GrantRoss and ttom, whose March 11th post dealt with the same problem.  My own suggestion for ttom was not effective.

                   

                  For Bob Yarnall's question, Excel will let you format a number-filled cell as text without using a leading apostrophe.  This can definitely cause problems when trying to match with a text cell that has the apostrophe.  Most finance and inventory applications have leading zero part or account numbers that have to have text formatting.

                   

                  Grant (R, not P), Excel's VLOOKUP is so painfully sensitive to tiny shades of difference in cell format that I often wind up reformatting my source and target lookup cells to General format and then applying desired formats until they match.  I've just switched from Excel 97 to Excel 2000 and sincerely hope that things have gotten better.  Keep us apprised of your situation.

                   

                  Now, unless someone comes up with a Monarch exporting trick (waiting?), we can rest easy that we're not dealing with a Datawatch problem!

                  • Apostrophe when exporting to Excel 2000
                    Nick Osdale-Popa

                    Ok, thanks for more insight into this Grant & Tom.

                    You can format a numeric cell with 000 and all single digits and double digit numbers entered will have 1 - 2 zero's leading the number. Also you can format a text cell with "0"@ and anything you enter in the cell will have a leading zero.

                     

                    Now this is where GrantRoss may be experiencing problems, but I may be wrong.

                     

                    Those formats are DISPLAY formats only and have nothing to do with the underlying value.

                     

                    Thus if a number in the text cell example I gave is 5, it will display as 05, and if you compare that to a text list of numbers that has a leading zeros, it will be trying to match the number 5 to the list and not 05.

                     

                    I'd be interested in knowing how his cell is formatted that he is doing the VLOOKUP formula against.

                     

                    Hope that makes sense.

                     

                    [size="1"][ March 19, 2003, 06:07 PM: Message edited by: Nick Osdale-Popa ][/size]

                    • Apostrophe when exporting to Excel 2000
                      RGBrooks _

                      Extract the field as a numeric field and add the leading zero's in Excel. That will keep the Excel properties as numeric and you shouldn't have any problems between text and numeric lookups.

                      To add the leading zeros within an excel spreadsheet: highlight the cells where you need the leading zero's and go to format>cell>format>custom; then under "type:" enter the number zero for the total places of numbers including leading zero's.

                      Example if you want the number to display 00215, you would enter 00000. That will put in the leading zero's and keep the cell numeric. Hope this helps.