exel
Microsoft Office TrainingThe UK's Number 1 for Microsoft Office Training Sitemap add this page to your favourites/bookmarksBookmark page

view a printable version of this pagePrintable version
Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Exel 2007

Exel 2007

The UK's most regular instructor-led training courses.
Training information: excel-xp-training · Microsoft excel training · Microsoft Excel Training UK
See also · excel-courses-london · excel courses in london · excel microsoft training

resolvedResolved · Urgent Priority · Version 2007

No ranking yet
5 posts
replyReplyFri 8 May 2009, 14:05Delegate Nestor said...

Nestor has attended:
Excel 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.

Excel Advanced 1 day course
Version Date Location Places
available
Book Next place rate (£) List
price
Pay by
Card
Pay by
Invoice
2007 Mon 2 Aug 2010 Southwark 3 Book now £240 £245£245
2003 Thu 5 Aug 2010 Southwark 4 Book now £240 £245£245
2010 Fri 6 Aug 2010 Bloomsbury 0 FULL    £299
2007 Tue 10 Aug 2010 Bloomsbury 0 FULL    £245
2003 Tue 10 Aug 2010 Limehouse 5 Book now £235 £240£245
2007 Fri 13 Aug 2010 Limehouse 8 Book now £229 £234£245
Full Schedule: See all 134 Excel Advanced course dates.
Bookings currently available until 28th September 2011.

Diamond
2,024 posts
replyReplyFri 8 May 2009, 14:20Trainer Amanda said...

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

No ranking yet
5 posts
replyReplyFri 8 May 2009, 14:47Delegate Nestor said...

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.

Diamond
2,024 posts
replyReplyFri 8 May 2009, 14:59Trainer Amanda said...

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

No ranking yet
5 posts
replyReplyFri 8 May 2009, 15:15Delegate Nestor said...

RE: Exel 2007

Any luck at all.

Thanks,

Nestor.

Diamond
2,024 posts
replyReplyFri 8 May 2009, 15:13Trainer Amanda said...

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

No ranking yet
5 posts
replyReplyFri 8 May 2009, 15:22Delegate Nestor said...

RE: Exel 2007

Hi Amanda,

This time it works, thank you very much for your help and your patience, very appreciated.

Best regards,

Nestor.


Related articles

· VBA Excel 2007
· Simple Ways To Customise Excel
· Microsoft Office and the Small Business: Part 1 Excel
· Reporting and Analysis with Excel
· Utilise the web's information banks with Microsoft Excel

Please browse our web site to find out more about
excel-xp-training and other Microsoft training courses.

Excel tip:

Change the Value of a Constant

When using a named constant in a worksheet, you may wish to change the value of that constant.

From the 'Insert' menu, select 'Name', then select 'Define'.

In the 'Define Name' dialog box, select the constant that you want to change.
Change the value in the 'Refers To' box.
Click OK.

Wherever that named constant has been used it will now use its new value.

View all Excel hints and tips


Rate this page:
2.2/5 (127 votes cast)
Institute of IT Training - Accredited Training Provider ILM
Microsoft Certified Partner
Microsoft Office Specialist Authorised Testing Centre (MOS and MCAS)

Prodigy Platinum Learning Partner

Institute of IT Training - Accredited Training Provider
McAfee Secure sites help keep you safe from identity theft, credit card fraud, spyware, spam, viruses and online scams
Association of Computer Trainers verified by visa, mastercard securecard

Mini sitemap. These are the main areas of our web site. Full sitemap.

Professional & Management

Professional Skills courses
Project Management Course London
Project Management Courses London
Project Management Training London
Project Management Training
Project Seminar
Project Seminars
Time Management Course London
Time Management London
Time Management Courses London
Time Management Training London
Introduction to Finance course
Assertiveness Skills course
Effective Communications Skills training
Presentation Skills London

Training Formats

Public scheduled courses
On-site training
Closed company courses

MS Office & IT Applications

Excel courses
Excel training
Excel Training Courses Medway
Microsoft Excel training
Microsoft Project training
Microsoft Office training
Microsoft Outlook training
Microsoft Powerpoint training
Microsoft Word training
MS Project courses
MS Project training
Outlook courses
PowerPoint courses
PowerPoint training
VBA courses
Word courses
(more...)

Crystal Reports training

DreamWeaver courses
Dreamweaver training

Training Information

London Computer Training
Computer Training London

Training venues
Client list
FAQ
Pricing and availability
Course details / Syllabus

Training Information
Training Articles
Microsoft Training Blog

Interested in Access training?
Please see the following pages:

Access courses
Access training
Microsoft Access training
Microsoft access courses
Microsoft training access course
Microsoft+access+training
Access courses in london