+ Reply to Thread
Results 1 to 11 of 11

Making a number have Text format

  1. #1
    Howard Kaikow
    Guest

    Making a number have Text format

    I am inserting stuff into some cells as follows:

    shtExcel.Range(Cells(lngRow, 1), Cells(lngRow, 5)) = Split(strBuffer, vbTab)

    Then, I change the cells in columns 2-5 to numeric formattrying to change
    some of the cells to numeric format. using code like

    With shtExcel.Range(Cells(lngFormat, 2),Cells(lngFormat + lngHigh, 3))
    .Value = .Value
    End With

    Then assigning the relevant format for the cells in eaxh column.

    However, the cells in column 1 are supposed to retain Text format, but some
    cells have all numbers, e.g., 000, and are intended to stay as Text.

    If I set Application.ErrorCheckingOptions.NumberAsText = false, I achieve my
    goal while the workbook is open.
    However, if I save the workbook, exit Excel, then re-open the workbook, the
    NumberAsText propety reverts to whatever the user has chosen.

    How can I programmatically change numbers to Text format?
    If I save the value in a string variable, then assign to the Value property,
    the cell is treated as a number, e.g., 000 becomes 0.

    Is there a way to do the deed without changing a user's NumberAsText
    property?




    --
    http://www.standards.com/; See Howard Kaikow's web site.



  2. #2
    Registered User
    Join Date
    12-28-2005
    Posts
    7

    It may help you.

    I did not understand your problem correctly but whenever and where ever you need to conver a value to text format you may use CStr function. Example is as follows:
    Range("A2")=Cstr(Range("A1")

  3. #3
    Howard Kaikow
    Guest

    Re: Making a number have Text format

    "gajendra_vba" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > I did not understand your problem correctly but whenever and where ever
    > you need to conver a value to text format you may use *CStr* function.
    > Example is as follows:
    > Range("A2")=Cstr(Range("A1")


    I tried

    For Each rngCell In .Range(Cells(lngFormat, 1),
    Cells(lngFormat + lngHigh, 1))
    With rngCell
    .Value = CStr(.Value)
    End With
    Next rngCell

    Did not change the format.



  4. #4
    Howard Kaikow
    Guest

    Re: Making a number have Text format

    I cannot use NumberAsText anyway, because that property is not in Excel 97
    or Excel 2000.
    Property is only in Excel 2002 and 2003.

    I would prefer to support all 4 versions, tho I'd be willing to ditch
    support for Excel 97, if there was no alternative.



  5. #5
    Tom Ogilvy
    Guest

    Re: Making a number have Text format

    Is there a single command that will convert a multicell range with numerical
    entries from values stored as numbers to values stored as text? I don't
    believe there is.

    You can always loop and prepend a single quote.

    --
    Regards,
    Tom Ogilvy

    "Howard Kaikow" <[email protected]> wrote in message
    news:[email protected]...
    > I cannot use NumberAsText anyway, because that property is not in Excel 97
    > or Excel 2000.
    > Property is only in Excel 2002 and 2003.
    >
    > I would prefer to support all 4 versions, tho I'd be willing to ditch
    > support for Excel 97, if there was no alternative.
    >
    >




  6. #6
    Howard Kaikow
    Guest

    Re: Making a number have Text format

    "Tom Ogilvy" <[email protected]> wrote in message
    news:uOO7%[email protected]...
    > Is there a single command that will convert a multicell range with

    numerical
    > entries from values stored as numbers to values stored as text? I don't
    > believe there is.
    >
    > You can always loop and prepend a single quote.


    Thanx, I fergot about that, I'll give it a try.



  7. #7
    Howard Kaikow
    Guest

    Re: Making a number have Text format

    I tried

    .Range(Cells(lngFormat, 1), Cells(lngFormat + lngHigh,
    1)).NumberFormat = "@"
    For i = 0 To lngHigh
    With Cells(lngFormat + i, 1)
    If IsNumeric(.Value) Then
    strTemp = CStr(.Value)
    .Value = "'" & strTemp
    End If
    End With
    Next i

    Excel still insists on marking the cells as errors.

    The only real issue is how to programmatically tell Excel to ignore the
    "error" for those cells.
    Is there a way to do that?

    The NumberAsText property can be overridden by the user's options, not to
    mention the property is not supported by Excel 97 and 2000.



  8. #8
    Howard Kaikow
    Guest

    Re: Making a number have Text format

    It's been a while, so I guess there's no solution.



  9. #9
    Peter Beach
    Guest

    Re: Making a number have Text format

    Hi Howard,

    Don't see the original post :-(

    Do you mean something like:

    Sub b()
    Dim s As String

    s = Format$(1.2345, "$0.000")
    Debug.Print s
    End Sub

    or

    Sub c()
    Dim s As String

    s = Format$(1234568, "00-00-00-00")
    Debug.Print s
    End Sub

    Perhaps I'm missing something. As I say I can't see your original post.

    Regards,

    Peter Beach

    "Howard Kaikow" <[email protected]> wrote in message
    news:[email protected]...
    > It's been a while, so I guess there's no solution.
    >
    >




  10. #10
    Howard Kaikow
    Guest

    Re: Making a number have Text format

    "Peter Beach" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Howard,
    >
    > Don't see the original post :-(
    >
    > Do you mean something like:
    >
    > Sub b()
    > Dim s As String
    >
    > s = Format$(1.2345, "$0.000")
    > Debug.Print s
    > End Sub
    >
    > or
    >
    > Sub c()
    > Dim s As String
    >
    > s = Format$(1234568, "00-00-00-00")
    > Debug.Print s
    > End Sub
    >
    > Perhaps I'm missing something. As I say I can't see your original post.



    The following is the original post.
    ---------------------------
    I am inserting stuff into some cells as follows:

    shtExcel.Range(Cells(lngRow, 1), Cells(lngRow, 5)) = Split(strBuffer, vbTab)

    Then, I change the cells in columns 2-5 to numeric formattrying to change
    some of the cells to numeric format. using code like

    With shtExcel.Range(Cells(lngFormat, 2),Cells(lngFormat + lngHigh, 3))
    .Value = .Value
    End With

    Then assigning the relevant format for the cells in eaxh column.

    However, the cells in column 1 are supposed to retain Text format, but some
    cells have all numbers, e.g., 000, and are intended to stay as Text.

    If I set Application.ErrorCheckingOptions.NumberAsText = false, I achieve my
    goal while the workbook is open.
    However, if I save the workbook, exit Excel, then re-open the workbook, the
    NumberAsText propety reverts to whatever the user has chosen.

    How can I programmatically change numbers to Text format?
    If I save the value in a string variable, then assign to the Value property,
    the cell is treated as a number, e.g., 000 becomes 0.

    Is there a way to do the deed without changing a user's NumberAsText
    property?

    ------------------------------
    And I followed this with the following post:
    ------------------------------------------
    I tried

    .Range(Cells(lngFormat, 1), Cells(lngFormat + lngHigh,
    1)).NumberFormat = "@"
    For i = 0 To lngHigh
    With Cells(lngFormat + i, 1)
    If IsNumeric(.Value) Then
    strTemp = CStr(.Value)
    .Value = "'" & strTemp
    End If
    End With
    Next i

    Excel still insists on marking the cells as errors.

    The only real issue is how to programmatically tell Excel to ignore the
    "error" for those cells.
    Is there a way to do that?

    The NumberAsText property can be overridden by the user's options, not to
    mention the property is not supported by Excel 97 and 2000.



  11. #11
    Stephen Bullen
    Guest

    Re: Making a number have Text format

    Hi Howard,

    > The only real issue is how to programmatically tell Excel to ignore the
    > "error" for those cells.
    > Is there a way to do that?


    That would be the Errors collection for the range:

    rng.Errors(XlErrorChecks.xlNumberAsText).Ignore = True

    Regards

    Stephen Bullen
    Microsoft MVP - Excel

    Professional Excel Development
    The most advanced Excel VBA book available
    www.oaltd.co.uk/ProExcelDev



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1