PDA

View Full Version : Sleeper: Auto formatting



austenr
05-25-2005, 09:50 AM
I have a problem with using the columns.autofit. In certain rows I have a very large number with about 20 decimal places which changes everytime the report is ran. When importing the text file, it comes out as "Somenumber" + 20. In rows immediatly following this cell, are names I need to autofit. How Can I get around this?

byundt
05-25-2005, 10:52 AM
Excel double precision is limited to 15 digits. Any more than that will be lost to an approximation. So if you want to keep them all (such as for a credit card number), then you'll need to format the cell as text before entering the number.

When Excel has a very long number to fit in a small column width, it tries to use Scientific notation. I suspect you are seeing something like this: 1.2813E+20 And once this conversion happens, the Format...Columns...AutoFit isn't going to help. Once again, the cure is to format the cell as text before you enter the data.

One way of entering a long number (as text) into a cell formatted as General is to prefix the number with a single quote:
'1234567890123456789

Brad

austenr
05-25-2005, 12:26 PM
What may be an easier solution is to strip the last 6 characters off of the back of the data in Column A and use that for the cell value. Any ideas?

austenr
05-25-2005, 02:20 PM
I got this to work but it wont work if it tries to pass a cell with alpha text. What is a way around that?


Sub CheckForNumeric()
Dim newString As String
Dim I As Integer
I = 1
While Worksheets("Sheet2").Range("A" & I) <> ""
newString = Worksheets("Sheet2").Range("A" & I)
newString = Right(newString, Len(newString) - 10)
Worksheets("Sheet2").Range("A" & I) = newString
Wend
End Sub