Formatting of Cells In Excel

[loo]

Excel 2000 currently I am trying to figure out how to keep a long number to remain as a text field in excel. When it imports from crystal reports it comes in as a text field. Excel picks it up as a number stored as text. If you go into that cell and out it coverts it to 2.00201E+12
I need these type of cells to remain as 2002010301951
for an export to Access. This same situation occurs if you CONCATENATE an ' with a number and paste special with value only into a preformatted cell of text. Any suggestions on how to stop this?

 

[AngO]

loo,
I am not familar with Crystal, and how it exports.
The only way I know around the scientific notation is as follows:
If you change the format of the column/row to text, the scientific notation remains. UNTIL - you edit each cell. If you double click, then exit the cell, the whole number will show up. Alternatively you can also press F2, and enter or tab if you like the keyboard.

If you have a large number of cells, this will be a hassle, maybe someone out there knows of VB code to go through the cells for you. . . .?

Best of Luck,
AngO

 

[euskadi]

Can't you just select the range of cells and do /Format/Cells Number (0 decimal places)?? That won't keep it as text, but it should display properly on screen.

 

[DaleWatson123321]

loo,

I've tested the following process, and it works.

1) If your data is in A, enter the following formula in B1: =INT(A1)

2) In C1, enter this formula: =RIGHT(B1,999)

3) Copy the formulas in B & C down for all your records.

4) Click on the "D" to select the entire column, and format as "Text" (trust me, it'll work THIS time, even though it didn't before).

5) Click on "C" to select the entire column

6) <Control> C to Copy

7) Click on &quot;D&quot;, and Paste Values - <Alt> E - S - V <Enter>

NOW, when you check the results in column D, by using <Edit> <Enter>, you'll see that the number REMAINS as TEXT.

Hope you find this useful. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

loo,
I have had similar problems with a field of 12 digits.

If you can live with the value being formatted Not Text then...
use Number formatting with zero places

Otherwise either...
1. create a new column with the formula...

="'"&A1

assuming that Column A has your numeric text, or

2. use this code to CHANGE the value in the column to include the singel quote...

Sub NumericDigits()
    Dim rng As Range, cell As Range
    With ActiveCell
        Set rng = Range(Cells(.Row, .Column), Cells(.Row, .Column).End(xlDown))
    End With
    For Each cell In rng
        With cell
            .Value = "'" & .Value
        End With
    Next
End Sub

Select the cellin the first row in the column containing the numeric text data and then run the procedure.

Hope this helps Skip,
metzgsk@voughtaircraft.com