Hi all

I am reading a text code (12 characters) taking the middle four
characters and need to re-output them to a certain place in the
spreadsheet. This has to be done programatically, since other
calculations are used and decisions made whether the code is needed or
not (ie i cannot use a formula on the worksheet or formatting to
resolve this issue).

Example code would be "AB2101054321" and "AB2099054321"

now, in vb, I read the codes in using a variable (declared as a
string) then take the 4 characters, starting four from the left ie
mid(code,4,4) to subsequently re-output (if necessary) to a cell in a
spreadsheet.

The problem I am encountering is that for the first code, it works
fine - ie 1010, but the second comes out as 990 when using
activecell.value.

I need the output as text to be 0990.

Can anyone help with this please?

On Thu, 16 Aug 2007 23:33:39 -0000, Wild Jim
wrote:

Hi all

I am reading a text code (12 characters) taking the middle four
characters and need to re-output them to a certain place in the
spreadsheet. This has to be done programatically, since other
calculations are used and decisions made whether the code is needed or
not (ie i cannot use a formula on the worksheet or formatting to
resolve this issue).

Example code would be "AB2101054321" and "AB2099054321"

now, in vb, I read the codes in using a variable (declared as a
string) then take the 4 characters, starting four from the left ie
mid(code,4,4) to subsequently re-output (if necessary) to a cell in a
spreadsheet.

The problem I am encountering is that for the first code, it works
fine - ie 1010, but the second comes out as 990 when using
activecell.value.

I need the output as text to be 0990.

Can anyone help with this please?

Depending on just what you want to do with the result, you can either output
the value as a text string, or format the cell as "0000"

Note some of the different methods:

=======================================
Sub output()
Const n1 As String = "'0990" 'Note the single quote
Const n2 As Long = "0990"
Const n3 As String = "0990"


[f1].Value = n1
[f2].Value = n2
[f2].NumberFormat = "0000"

[f3].NumberFormat = "@"
[f3].Value = n3

End Sub
================================
--ron