ActiveCell.Value

[tmarsh7407]

I am assigning a two character string to a variable as follows (Stringy is
the variable I read each record form before breaking it into pieces):

ThisActy = Mid(Stringy, 4, 4)

The record I am reading from has "00 " (two zeros and two blanks) starting
at position 4. I then try to insert it into a cell in my spreadsheet as
follows:

ActiveCell.Value = ThisActy

However when I look at the cell I see a 0 (number, not text) in that cell.
I want it to stay in text form in the spreadsheet. What am I doing wrong?

 

[JLGWhiz]

Looks like a built in glitch. Here is how I handled it.

Sub jl()
st = Mid(Range("B2"), 4, 4)
st = CStr(st)
Range("A2").NumberFormat = "@"
Range("A2") = st
End Sub

The receiving range has to be formatted as text or it will automatically
convert the "00 " to a number which = 0.

 

[Dave Peterson]

Have you ever typed
1234_____
( _ = spacebar)
into a cell.

Excel is very forgiving. It'll let you do it, but fix it to be just 1234 (a
real number).

Same thing happens when you plop anything into a cell formatted as General
(non-text).

You could use what JLGWhiz suggested:

with activesheet
.range("a1").numberformat = "@" 'text
.range("A1").value = Mid(stringy, 4, 4)
End With

Or just like you use apostrophes when you're typing something into the cell:

activesheet.range("a1").value = "'" & Mid(stringy, 4, 4)