Avoid Convert to Number

Options
I frequently export Raiser's Edge Exports to Excel.  From there I often do a Vlookup on the constituent ID.  I am required to convert the Constituent ID column to numbers in order to complete this VLookup. Althuogh this is not too many keystrokes, I would prefer to avoid this step altogether. Perhaps there is something I am overlooking in the way I export which would make this converstion process unnecessary. I would elcome ideas.
Tagged:

Comments

  • Amy Dana
    Amy Dana Community All-Star
    Tenth Anniversary 500 Likes 100 Comments Photogenic

    Bob Wiebe:

    I frequently export Raiser's Edge Exports to Excel.  From there I often do a Vlookup on the constituent ID.  I am required to convert the Constituent ID column to numbers in order to complete this VLookup. Althuogh this is not too many keystrokes, I would prefer to avoid this step altogether. Perhaps there is something I am overlooking in the way I export which would make this converstion process unnecessary. I would elcome ideas.

    How are you converting them now? And what exactly are you looking for with the VLookup? I'm wondering if there is a way you can query on just those records instead of manipulating an Excel spreadsheet.

  • Rather than bothering to change the format of the constituent IDs, I usually alter my Vlookup slightly to search for the ID in a different format. For example, if the ID in A1 was in number format and the list being searched is formatted as text, then instead of looking up cell A1 I would use A1&"" (A1 concatenated with an empty string) , or VALUE(A1) if the ID is in text format and the other list is formatted as numbers.


    If you've only got one or two columns of Vlookups then this might be quicker than how you're currently doing it. Presumably for very large spreadsheets it adds on a bit of calculating time, but usually I'm just cross-referencing small lists so I'm not too fussed about that.
  • Bob Wiebe:

    I frequently export Raiser's Edge Exports to Excel.  From there I often do a Vlookup on the constituent ID.  I am required to convert the Constituent ID column to numbers in order to complete this VLookup. Althuogh this is not too many keystrokes, I would prefer to avoid this step altogether. Perhaps there is something I am overlooking in the way I export which would make this converstion process unnecessary. I would elcome ideas.

    Yes, RE exports to Excel with that leading apostrophe that forces Excel to look at every cell as plain text. To avoid this problem, what I do (and what I recommend to all our users here) is export to character separated values, then open the resulting .CHR file in Excel.  Excel is remarkably good at interpreting data types from plain text files, and it almost always figures out what's a number, text, date, currency, etc.

  • Alan French:

    Rather than bothering to change the format of the constituent IDs, I usually alter my Vlookup slightly to search for the ID in a different format. For example, if the ID in A1 was in number format and the list being searched is formatted as text, then instead of looking up cell A1 I would use A1&"" (A1 concatenated with an empty string) , or VALUE(A1) if the ID is in text format and the other list is formatted as numbers.


    If you've only got one or two columns of Vlookups then this might be quicker than how you're currently doing it. Presumably for very large spreadsheets it adds on a bit of calculating time, but usually I'm just cross-referencing small lists so I'm not too fussed about that.


    Thanks, Alan. This looks promising.

     

Categories