Solved

I don´t want cientific notation in my text data!

Posted on 2013-06-25
6
392 Views
Last Modified: 2013-06-25
I need to import an excel table to access. I have data that are product codes (all text). It´s the first column of my table. The numbers cells  appeared as numbers and cells with letters appeared  as text in my original excel table.

I decided to convert everything to text, with format cell "number" and "text" within excel. All cells appeared to be converted to text but some codes were written in cientific notation. I guess the digits product codes with more than 10 digits.

It seems so simple but I don´t konw how to keep it simply text! I tried to locate and replace but excel doesn´t locate the +E that I see on cells.

I send an example file (my file has more than 24.000 lines and I can´t make it one by one).

Please no programming on answers. I am just an (poor) ordinary excel user.

I am using office 2007

Thanks
Pasta1.xlsx
0
Comment
Question by:JULIASPHESS
6 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 39276190
Insert a column to A:A
Type into A1... =""&B1
Copy the formula down.
Highlight column B:B
Format as text
Copy A:A
Paste special Values from A:A to B:B
Delete A:A.
Done.
Pasta1.xlsx
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39276195
Format the Excel column(s) as Text.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39276197
Hi,

If you prefix cell [A8] with an apostrophe [ ' ] character the text is displayed as intended without the Scientific notation (as The_Barman mentioned above).

'116460051078

BFN,

fp.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 12

Expert Comment

by:telyni19
ID: 39276203
Here's how I would fix this kind of thing:
Add a new column after column A. Format the whole column as "General".
Insert the following formula and copy down the column:
=TRIM(A2&" ")
Copy column B and paste over itself as values.
Transfer over any other important information from column A, like the column header, to column B.
Delete column A.

Attached is your sample converted in this way. The formula forces Excel to treat the values as text.
Pasta1-1.xlsx
0
 

Author Closing Comment

by:JULIASPHESS
ID: 39276255
Love it, can you explain the command =""&A1?
0
 
LVL 24

Expert Comment

by:Steve
ID: 39276279
This does the same as
=TRIM
=CLEAN
=CONCATENATE
=UPPER
... or other similar Text functions...
Adding double quotes indicates the result to be text...
In all cases, the result MUST be Text as far as Excel is concerned so it will not allow it to be a number, scientific or otherwise.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now