Format cell (column) with 16 digits in a macro

[cue ball]

Good morning,
My macro is reading a text file and creating a new excel sheet. The one problem is with column "D". The text file for this column contains 16 characters (numbers) and the resulting column appears in scientific notation format. i.e. <TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 48pt; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=20 width=64 align=right>
6.42E+15
</TD></TR></TBODY></TABLE>
<!-- / message -->

 

[pgc01]

Hi

Excel allows for a number a maximum of 15 digits.

Since you have 16 digits, format the cell as text before inserting the value.

 

[cue ball]

Hi,
Thanks for the response. I can do it fine with the import wizard but how do I code it in a macro?
Thanks

 

[GlennUK]

Show your macro code.

 

[cue ball]

Cheers,

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
If Len(TempVal) >= 16 And Right(TempVal, 1) >= "0" And _
Right(TempVal, 1) <= "9" Then
Cells(RowNdx, ColNdx).Value = Format(TempVal, "Text")
Else
Cells(RowNdx, ColNdx).Value = TempVal
End If
If TempVal < 0 Then
Cells(RowNdx, ColNdx).Font.Color = RGB(255, 0, 0)
Cells(RowNdx, ColNdx).HorizontalAlignment = xlRight
End If
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

 

[GlennUK]

Put:

Cells(RowNdx, ColNdx).NumberFormat = "@"

before your highlighted row.

 

[cue ball]

Glen,
I appreciate your knowledge. You nailed it. I had tried that before except I had filled the cell before the numberformat. Now that I have the statements in the correct order it works perfectly. Thanks again

 

[GlennUK]

That's great!