Nestor has attended:
Excel VBA Intro Intermediate course
Exel 2007
Hi we are having a nightmare here working with CUSIP codes in Excel 2007. CUSIP codes can be a series of 9 digits witha letter E in any popsition. When we retrive those codes from our system and paste them into EXCEL 2007 the are converted to scientific format as below.
Orignal Codes
83437E207
74880E109
60783E106
35728E106
88959E105
882135E77
882117E67
Excel convert them to
8.34E+211
7.49E+113
6.08E+110
3.57E+110
8.90E+109
8.82E+82
8.82E+72
We have tried formating the cells to TEXT before pasting the codes into Excel but it doesn't work.
Cound you please advise?
Is there any way to STOP Excel converting those codes to scientific format ?
Thanks,
Nestor.
RE: Exel 2007
Hello Nestor
Thank you for your question.
Have you tried creating a custom number format for the column (or row) the data is being pasted into?
If you select the column/row/cells that the data is going to be pasted into; then go into your Number Formats (where you have been choosing Text as your format); select Custom instead of Text, and in the box under where it says Type: enter nine zeros (000000000) or nine hashes (#########); click OK. Then try pasting your data.
I'm not sure about whether there's any way of stopping Excel pasting in the format it chooses by default, I'll look into this.
Let me know if the custom number format works for you.
Kind regards
Amanda
RE: Exel 2007
Hi Amanda thanks for your fast response.
I have tried as you advised Custom instead of Text, and in the box under where it says Type: entering nine zeros (000000000) or nine hashes (#########); click OK.
But it doesn't work is just convert the code 83437E207 into the long enteger number and what we need is to keep the code as it is 83437E207.
Thanks,
Nestor.
RE: Exel 2007
Oh dear, I hoped that would help.
A bit long winded, but what if you paste the codes into a text (Notepad) file; then import the file into Excel either by opening the file within Excel; or using the Text to Columns wizard (on the Data tab)?
Kind regards
Amanda
RE: Exel 2007
Hi Nestor
I just tested the following, it seems to work.
1. Copy and paste the codes into Notepad.
2. Copy the codes from Notepad.
3. Paste into Excel.
4. Click the Paste smart tag that appears in the bottom right under the pasted codes.
5. Select Import Text Wizard.
6. At step 1 of the import wizard, check File origin says Windows (ANSI); click Next.
7. Don't change anything at the next screen; click Next.
8. At step 3, select Text under Column Data Format.
9. Click Finish.
The codes should appear as you wish. Click the ! smart tag, select Ignore Error to make the green triangles disappear.
Hope this works for you.
Kind regards
Amanda
