Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Format Cells, huh?

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Minneapolis, MN
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    mm (for month)

    dd (for day)
    ~Anne Troy

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

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

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




  4. #4
    New Member
    Join Date
    May 2002
    Location
    Minneapolis, MN
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    May 2002
    Location
    Minneapolis, MN
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    New Member
    Join Date
    May 2002
    Location
    Minneapolis, MN
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    San Ramon CA
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •