Format Cells, huh?

[nolc]

How do I format a Number cell from General to Text? I need xlsheet.Range("A1:A" & lastusedrow) & xlsheet.Range("C1:C" & lastusedrow) to display "08" not "8"..."082500" not "82500".

I've tried :
xlsheet.Range("A1:A" & lastusedrow).NumberFormat = "Text"

This has got to be an easy question...Also, please look for my next question.

Much thanks,

[Anne Troy]

Assuming this is a date and you want the Month or Day from it, use custom format:

mm (for month)

dd (for day)

[waderw]

Try this:

xlsheet.Range("A1:A" & lastusedrow).NumberFormat = "@"

or
xlsheet.Range("A1:A" & lastusedrow).NumberFormat = "00"

[nolc]

xlsheet.Range("A1:A" & lastusedrow).NumberFormat = "@"
or
xlsheet.Range("A1:A" & lastusedrow).NumberFormat = "00"

Above is a good solution, BUT it doesn't change the value. I need the value to read "082323" and not "82323", because this data is then uploaded to Oracle...SO I think I will run into problems. Any ideas?

[waderw]

try this:

Selection.NumberFormat = "0000000"

change the "0000000" to however many places you need or if you just want to add a "0" infront of it then try:

dim newstring,oldstring as string

oldstring = activecell.value
newstring = "0" & oldstring

[nolc]

That's basically what I'm doing in my code.

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Dim co_code As String

For i = 1 to UBound(xlArray)

xlsheet.Range("A" & i) = co_code
xlsheet.Range("B" & i) = xlArray(i, 1)

xlsheet.Range("C" & i) = co_code & xlArray(i, 2)

xlsheet.Range("D" & i) = xlArray(i, 3)

Next i
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Is there away to format a whole Column programmatically to "Text"?

[ This Message was edited by: nolc on 2002-05-02 14:01 ]

[ This Message was edited by: nolc on 2002-05-02 14:01 ]

[waderw]

Try:
Columns("A:D").select
selection.numberformat = "@"
range("A1").select

[nolc]

I figured it out! Within my For Loop I concatenated an apostrophe to the value. The code is below....thanks.

For i = 1 to UBound(array)

xlsheet.Range("C" & i) = "'" & co_code
xlsheet.Range("C" & i) = "'" & co_code & array(i, 1)

Next i

[waderw]

you might want to remove the 'i' for effeciency. the compiler knows that you are referring to the for loop.

[g_erhard]

Not to stifle innovation or anything, but, what about using the TEXT function? I think it might prove simpler.

IF:
A1 = 55
and B1 contains the formula:
Text(A1, "00000")

after calculation B1 will contain the string "00055".

Copying or exporting B1 will result in the desired text string, including all leading zeros.

If you just need to pad the number with one leading zero, try this formula:

"0" & TEXT(A1, "@")


(eek!) the second form 'breaks' if you give it a negative number, the first does not.
_________________


[ This Message was edited by: g_erhard on 2002-05-03 09:16 ]

[ This Message was edited by: g_erhard on 2002-05-06 07:38 ]