2 Replies Latest reply: May 15, 2014 9:59 AM by DZizzo _ RSS

    External lookup and Excel SSN format

    DZizzo _

      Has anyone come across this problem before:  I am using an external lookup to an Excel database containing Social Security Numbers which are formatted using Excel's "number-special-ssn" format.  This displays as xxx-xx-xxxx in Excel, even if only numbers were entered originally.


      My problem is that Monarch reads how the numbers were entered (either as all numbers or as texts with dashes) and will match up either the number or the text format, not both.  It won't work to reformat the column in Excel, since that keeps either the number or the text format.


      I would appreciate any ideas on how to get around this so that Monarch can match both the number and the text format.  Maybe I'm dreaming, but thanks for any help!

        • External lookup and Excel SSN format
          Grant Perkins

          Monarch will be expecting to perform the lookup using a link which is either numeric or alpha but not a mix of the two. That said one of the issues you have anyway is that some look up cells will have more characters than others even if converted to text and therefore the TRIM functions may come into play.


          Presumably on the Monarch side the fields used on the link definition are all the same format - character or numeric. Even if they were a mixture you need to normalise the fields on both sides of the lookup if consistency is to be assured.


          In this case the Excel cells need to be 'converted' to either all numeric (strip the "-"'s from the text entered version and convert the cell from text to number) or all text(as above but convert all cells to text at some point).


          The logical thing to do would be to add another column to convert the link cell values using a formula to re-format them, and then link to Monarch using the new calculated column.


          The same requirement for Monarch of course but possibly a little easier to do depending on the source of the fields. (Not being an Excel expert, or anything close to that, it is what you would expect me to say!)


          I hope this helps.




          [size="1"][ January 23, 2006, 01:16 PM: Message edited by: Grant Perkins ][/size]

          • External lookup and Excel SSN format
            DZizzo _

            Thanks Grant.  This will work for us!  Your title should be Supreme Grand Master at least!