locked
"The Number in this cell is formatted as text" RRS feed

  • Question

  • Hello

    I have a column of numbers that are formatted as text (it has the little green triangle in the corner of the cell). How do I convert the column to numbers in my macro?

    Thank You

    TB

    Wednesday, March 30, 2011 9:58 PM

Answers

  • hi TB,

    For Each c In Range("A1:A" & Range("A65536").End(xlUp).Row)
     With c
        x = .Value
            .Clear
            .Value = x
     End With
    Next

    isabelle

    Le 2011-03-30 17:58, TBO2012 a écrit :

    Hello

    I have a column of numbers that are formatted as text (it has the little green triangle in the corner of the cell). How do I convert the column to numbers in my macro?

    Thank You

    TB

    • Marked as answer by Bruce Song Tuesday, April 12, 2011 9:39 AM
    Wednesday, March 30, 2011 10:30 PM
  • You could record a macro when you:
    Format the column as General
    Do a data|text to columns

    Choose General

    TBO2012 wrote:


    Hello

    I have a column of numbers that are formatted as text (it has the little green triangle in the corner of the cell). How do I convert the column to numbers in my macro?

    Thank You

    TB

    --

    Dave Peterson

    • Marked as answer by Bruce Song Tuesday, April 12, 2011 9:39 AM
    Wednesday, March 30, 2011 11:08 PM
  • Hi

    The following line of code changes column C to "general" thus removing the text format.  All you need to do is change the column to the column your using when you add it to your code.

        Columns("C:C").NumberFormat = "General"

    Hope this helps


    G North MMI
    • Marked as answer by Bruce Song Tuesday, April 12, 2011 9:39 AM
    Thursday, March 31, 2011 8:32 AM

All replies

  • hi TB,

    For Each c In Range("A1:A" & Range("A65536").End(xlUp).Row)
     With c
        x = .Value
            .Clear
            .Value = x
     End With
    Next

    isabelle

    Le 2011-03-30 17:58, TBO2012 a écrit :

    Hello

    I have a column of numbers that are formatted as text (it has the little green triangle in the corner of the cell). How do I convert the column to numbers in my macro?

    Thank You

    TB

    • Marked as answer by Bruce Song Tuesday, April 12, 2011 9:39 AM
    Wednesday, March 30, 2011 10:30 PM
  • You could record a macro when you:
    Format the column as General
    Do a data|text to columns

    Choose General

    TBO2012 wrote:


    Hello

    I have a column of numbers that are formatted as text (it has the little green triangle in the corner of the cell). How do I convert the column to numbers in my macro?

    Thank You

    TB

    --

    Dave Peterson

    • Marked as answer by Bruce Song Tuesday, April 12, 2011 9:39 AM
    Wednesday, March 30, 2011 11:08 PM
  • Hi

    The following line of code changes column C to "general" thus removing the text format.  All you need to do is change the column to the column your using when you add it to your code.

        Columns("C:C").NumberFormat = "General"

    Hope this helps


    G North MMI
    • Marked as answer by Bruce Song Tuesday, April 12, 2011 9:39 AM
    Thursday, March 31, 2011 8:32 AM
  • Hello

    I tried this and it appeared to work but the output was not what I expected.

    I don't fully understand what ("A1:A" & Range("A65536").End(xlUp).Row) is doing. However, the column I want to format to numbers is "E" and the first row is a title (text). How would this be defined in your line of code?

     

    Thank You

    Thursday, March 31, 2011 2:14 PM
  • Hello

     

    I figured it out...for anyone interested here is the code

    ("E2:E" & Range("E65536").End(xlUp).Row)

    Thank You for your help

     

    Thursday, March 31, 2011 2:25 PM
  • hi ,

    For Each c In Range("E2:E"&  Range("E65536").End(xlUp).Row) 'for the cells E2:Ex
     With c
        x = .Value
            .Clear    'deletes (content and format)
            .Value = CDbl(x)  'CDbl function converts a value to a types double (with decimal)
     End With
    Next

    isabelle
     Le 2011-03-31 10:14, TBO2012 a écrit :

    Hello

    I tried this and it appeared to work but the output was not what I expected.

    I don't fully understand what ("A1:A"&  Range("A65536").End(xlUp).Row) is doing. However, the column I want to format to numbers is "E" and the first row is a title (text). How would this be defined in your line of code?



    Thank You

    Thursday, March 31, 2011 2:44 PM
  • hi,

    yes, that's it

    another possibility, like Dave Peterson said

    Sub test()
    Set plg = Range("E2:E" & Range("E65536").End(xlUp).Row)
    plg.NumberFormat = "General"
    ' adapt the DecimalSeparator
    plg.TextToColumns Destination:=plg(1), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), DecimalSeparator:=".", TrailingMinusNumbers:=True
    End Sub

    isabelle

    Le 2011-03-31 10:25, TBO2012 a écrit :

    Hello



    I figured it out...for anyone interested here is the code

    ("E2:E"&  Range("E65536").End(xlUp).Row)

    Thank You for your help


    Thursday, March 31, 2011 3:48 PM