by Allen Wyatt
(last updated July 24, 2021)
Patrick works in the financial sector, and he often has to import files to Excel. Many times these files contain, as one of many fields, what is referred to as a CUSIP number. This value is nine characters long and can contain both letters and digits. When importing, Excel drops any leading zeros, and if there is a letter E near the end of the value, Excel may convert the value to scientific notation. Patrick needs the CUSIP numbers to be imported without modification, so he wonders if there is an elegant (and easy) way to do this.
There are actually three approaches you can use to get the information in that you want. All of the methods assume that you are working with a text file that was output by some other system, other than Excel. The idea behind the approaches is to make sure that Excel, during the import, identifies the field containing the CUSIP numbers as text, instead of trying to convert the field content to numeric values.
Modify the Source
The first method is to modify the source file that you are working with. This could be as easy as talking to whoever controls the software that creates the import file. If you can get them to change how the CUSIP number is generated so that, in the file, the number is preceded by an apostrophe, then importing should be a snap. The apostrophe informs Excel that the field is text, and it will treat it accordingly. Plus, the apostrophe won't show up on the imported data once it is in a worksheet.
You could also load the source file in a text editor and add the apostrophes yourself, but this may be a quite tedious task if there are many records in the file or if you need to work with many files over time.
Modify Your Traditional Import
Again, assuming you are importing a delimited file (either comma delimited to tab delimited), then you can follow these steps to have the file imported correctly:
Figure 1. The Text Import Wizard.
Use Power Query to Import the Data
Assuming you are using at least Excel 2016, you can use Power Query to load your data. Power Query seems to be all the rage these days, and it really does make the importation of data quite easy. Just follow these general steps:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13887) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Before you have your macro open and read a file from disk, you'll want to check to make sure it is really there. Here's ...Discover More
Need to know what the full path name is for the current workbook? With a simple macro you can display the full path name ...Discover More
If you import data into Excel that is created by other programs, you know that it can be bothersome to get your data ...Discover More
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.