Format Cells, huh?

nolc

New Member
Joined
May 1, 2002
Messages
9
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,
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Assuming this is a date and you want the Month or Day from it, use custom format:

mm (for month)

dd (for day)
 
Upvote 0
Try this:

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

or
xlsheet.Range("A1:A" & lastusedrow).NumberFormat = "00"
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
you might want to remove the 'i' for effeciency. the compiler knows that you are referring to the for loop.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top