Importing CUSIP Numbers Correctly

by Allen Wyatt
(last updated July 24, 2021)

2

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:

  1. Rename the import file so it has a .TXT filename extension. This step is often overlooked, but it will force Excel to display the Text Import Wizard, which is what you want to have happen.
  2. In Excel, display the Open dialog box. (In Excel 2007, click the Office button and then click Open. In Excel 2010, click the File tab of the ribbon and then click Open. In Excel 2013, click the File tab of the ribbon, click Open, then Computer, and finally Browse. In later versions of Excel, click the File tab of the ribbon and click Browse.)
  3. Using the Files of Type drop-down list at the bottom of the dialog box, indicate that you want to open Text Files (*.prn; *.txt; *.csv).
  4. Locate and select the file you renamed in step 1.
  5. Click on Open. Excel starts the Text Import Wizard, displaying the Step 1 of 3 dialog box. (See Figure 1.) (If the Text Import Wizard doesn't start, then you either aren't trying to import a text file or you didn't rename it as indicated in step 1.)
  6. Figure 1. The Text Import Wizard.

  7. Make sure the Delimited choice is selected, then click on Next. Excel displays the Step 2 of 3 dialog box.
  8. Make sure that either Comma or Tab are selected as a delimiter (depending on your file), then click on Next. Excel displays the Step 3 of 3 dialog box.
  9. At the bottom of the dialog box, click on the field that contains your CUSIP numbers. The entire column should now be selected.
  10. In the Column Data Format area, make sure the Text radio button is selected.
  11. Repeat steps 8 and 9 for any other fields that might confuse Excel. (For instance, if you data has additional CUSIP number fields.)
  12. Click on Finish. Your file is imported, and your CUSIP numbers should be intact.

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:

  1. Display the Data tab of the ribbon.
  2. In the Get & Transform Data group (left side of the ribbon), click From Text/CSV. Excel displays the Import Data dialog box. This looks very much like a standard Open dialog box.
  3. Using the controls in the dialog box, locate and select the source file that you want to import.
  4. Click Import. Excel "connects" to the data file and, shortly, displays a portion of your data in a large dialog box.
  5. Click the Transform button. Excel displays the Power Query Editor dialog box.
  6. Check to make sure that the appropriate columns (including the column containing your CUSIP numbers) are being interpreted as text. You can change these, as necessary.
  7. Click File | Close & Load To. Excel displays the Import Data dialog box.
  8. Make changes in the import settings, as desired. (For most uses, the defaults of Table and New Worksheet should be fine.)
  9. Click OK. Excel imports the data from the source file, placing it in a new worksheet.

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 Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Fixed-Width Settings when Converting Text to Columns

The Convert Text to Columns capabilities of Excel are very helpful when pulling apart information. When working with ...

Discover More

Selective Summing

If you want to add up the contents of a range of cells based on what is contained in a different range of cells, you need ...

Discover More

Smushing Text Together

Word gives you control over how your text appears on the page. This includes adjusting how close letters are to each ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Renaming a Workbook

Renaming a workbook from within Excel can seem daunting, but it is actually quite easy. All you need to do is use the ...

Discover More

Comma-Delimited Differences for PC and Mac

When you choose to save worksheet data in CSV format, Excel gives you three choices for file formats. Those choices are ...

Discover More

Invalid Names when Opening Workbook

Don't you hate opening a workbook and seeing error messages? If you see a message that some "invalid names" were detected ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 - 8?

2021-07-26 06:43:01

JMJ

@ Roy
Thanks for this hidden gem!
I do agree with you about the shortkeys, that changed with each and every version of Excel (You want to upgrade? Then re-learn them all!) and now tend to disappear...
J-M


2021-07-24 17:42:08

Roy

If you wish to use the decades old Text Import Wizard that is used in the first method (and that used to present itself without effort on your part but now... not so much), select:

Data | Get Data | Legacy Wizards | From Text (Legacy) (or press: Alt-A-PN-W-T)

No need to rename a file. I mention it because — for some WEIRD reason, people HATE that like they hate seeing "helper column" or "Named Range" or "macro" — even though you have the File | Open dialog up and are selecting the file, how hard is it to rename it right then and there, THEN select it?

I also find it of interest that when you hit "Alt" just to see the shortcut that goes with it for the menu, um, oh yeah, they call that menu "The Ribbon" like it isn't STILL just a menu, you DO NOT see the shortcuts for the next level and so are greatly discouraged from learning keyboard shortcuts to your choices. (Like who would EVER hit Alt-D to get the Data menu, then want to move over to the mouse and search further down the menu trees to the command he wants? Why wouldn't a mouse-y person like that just take the mouse from the start? If someone presses the Alt-D shortcut, he SURELY value (after relearning the pleasures of keying and never reaching over for the mouse (talk about slowing down!)) seeing the shortcut tags for the commands at each level of the menuing he accesses so even if he never plans to learn more than a few, he can always easily reach the DEEP submenu that does his desire without ever reaching off the keyboard... BECAUSE the labels are there and he never had to learn the key combinations (which he didn't really want to do) to use them! So if you mouse into the Ribbon, it should look like today, no keyboard help, just mouse-y choices. If you keyboard into it, the level you are on should ALWAYS show its shortcuts, no forcing it to required.

Anyway, if you eschew pressing the Alt-D after learning it will not actually show those next menu-level shortcuts, here's what you need (and for some reason ("some" 'cause I know MS stopped rewarding MS Expert shills for denigrating non-mouse-y use of their new menu a VERY long time ago) sites do not ever tell you how to get the next level going. (Once you get past the second menu level, individual choices actually often still use the "underscore a letter in a choice's description" mechanism, so you can use that, usually, for third-fourth-fifth and so on menu-levels finding your command.)

It is to press Alt-A. if wanting to use the Data submenu. Alt-M for the Formula submenu, Alt-R for the Review submenu, and so on. In other words, the old top level menu in Excel (2003 and before) had "underscore a letter" markings, A for Data, M for formula, and so on, that you used to go right into them and the Ribbon STILL does, though they mask it by not underscoring any letters. But they all still work, even ones that didn't exist in the usual top-level menu 2003 and prior offered. Experimenting (on a new, blank file...) will show you all of them as might a search of the internet.

In any case, press Alt-A instead of Alt-D and you get the tags shown for the second-level menu the Data tab on The ("I'm not a menu, I'm so not a menu, geez, will you stop hating me for being a menu that takes up a quarter of your screen, I mean no, I'm not even a menu anyway...") Ribbon rather than the basically useless simple showing of the Data submenu.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.