convert text to values

Discussion in 'Microsoft Excel Discussion' started by mp, Apr 25, 2012.

  1. mp

    mp Guest

    I know someone is going to say, just google it ;-) which I have and am
    sure it's there but I don't see it. Used to be a way in 97' to convert
    format of a cell from text to number

    (cells have existing values in it- xls downloaded from a site- numbers
    come in as text so I can't SUM(x:x) etc.

    Have searched Help and google but can't find using "convert text to
    number" and many variations of that...sure i'm missing the obvious, sorry
    Thanks
    Mark

    ps would then try to find a way to automate with vba (repeated task
    getting monthly/quarterly reports downloaded)
     
    mp, Apr 25, 2012
    #1
    1. Advertisements

  2. mp

    GS Guest

    mp wrote on 4/25/2012 :
    > I know someone is going to say, just google it ;-) which I have and am sure
    > it's there but I don't see it. Used to be a way in 97' to convert format of a
    > cell from text to number
    >
    > (cells have existing values in it- xls downloaded from a site- numbers come
    > in as text so I can't SUM(x:x) etc.
    >
    > Have searched Help and google but can't find using "convert text to number"
    > and many variations of that...sure i'm missing the obvious, sorry
    > Thanks
    > Mark
    >
    > ps would then try to find a way to automate with vba (repeated task getting
    > monthly/quarterly reports downloaded)


    Use the VALUE() function:

    =SUM(VALUE(X:X))

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 25, 2012
    #2
    1. Advertisements

  3. mp

    mp Guest

    On 4/25/2012 4:53 PM, mp wrote:
    > I know someone is going to say, just google it ;-) which I have and am
    > sure it's there but I don't see it. Used to be a way in 97' to convert
    > format of a cell from text to number
    >
    > (cells have existing values in it- xls downloaded from a site- numbers
    > come in as text so I can't SUM(x:x) etc.
    >
    > Have searched Help and google but can't find using "convert text to
    > number" and many variations of that...sure i'm missing the obvious, sorry
    > Thanks
    > Mark
    >
    > ps would then try to find a way to automate with vba (repeated task
    > getting monthly/quarterly reports downloaded)


    I found a site http://support.microsoft.com/kb/291047 with such helpful
    ideas as "reformat the cell and retype the value" :)
     
    mp, Apr 25, 2012
    #3
  4. mp

    mp Guest

    On 4/25/2012 5:12 PM, GS wrote:
    > mp wrote on 4/25/2012 :
    >> I know someone is going to say, just google it ;-) which I have and am
    >> sure it's there but I don't see it. Used to be a way in 97' to convert
    >> format of a cell from text to number
    >>
    >> (cells have existing values in it- xls downloaded from a site- numbers
    >> come in as text so I can't SUM(x:x) etc.
    >>
    >> Have searched Help and google but can't find using "convert text to
    >> number" and many variations of that...sure i'm missing the obvious, sorry
    >> Thanks
    >> Mark
    >>
    >> ps would then try to find a way to automate with vba (repeated task
    >> getting monthly/quarterly reports downloaded)

    >
    > Use the VALUE() function:
    >
    > =SUM(VALUE(X:X))
    >

    Thanks GS, also found I can manually click the error window after
    selecting bogus cells. Will look to vba it.
    Thanks
    mark
     
    mp, Apr 25, 2012
    #4
  5. mp

    GS Guest

    After serious thinking mp wrote :
    > On 4/25/2012 5:12 PM, GS wrote:
    >> mp wrote on 4/25/2012 :
    >>> I know someone is going to say, just google it ;-) which I have and am
    >>> sure it's there but I don't see it. Used to be a way in 97' to convert
    >>> format of a cell from text to number
    >>>
    >>> (cells have existing values in it- xls downloaded from a site- numbers
    >>> come in as text so I can't SUM(x:x) etc.
    >>>
    >>> Have searched Help and google but can't find using "convert text to
    >>> number" and many variations of that...sure i'm missing the obvious, sorry
    >>> Thanks
    >>> Mark
    >>>
    >>> ps would then try to find a way to automate with vba (repeated task
    >>> getting monthly/quarterly reports downloaded)

    >>
    >> Use the VALUE() function:
    >>
    >> =SUM(VALUE(X:X))
    >>

    > Thanks GS, also found I can manually click the error window after selecting
    > bogus cells. Will look to vba it.
    > Thanks
    > mark


    Sorry, I forgot to mention the formula I posted needs to be entered as
    an array formula. That means you need to use Ctrl+Shift+Enter so it
    works.

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 25, 2012
    #5
  6. mp

    GS Guest

    mp has brought this to us :
    > On 4/25/2012 5:12 PM, GS wrote:
    >> mp wrote on 4/25/2012 :
    >>> I know someone is going to say, just google it ;-) which I have and am
    >>> sure it's there but I don't see it. Used to be a way in 97' to convert
    >>> format of a cell from text to number
    >>>
    >>> (cells have existing values in it- xls downloaded from a site- numbers
    >>> come in as text so I can't SUM(x:x) etc.
    >>>
    >>> Have searched Help and google but can't find using "convert text to
    >>> number" and many variations of that...sure i'm missing the obvious, sorry
    >>> Thanks
    >>> Mark
    >>>
    >>> ps would then try to find a way to automate with vba (repeated task
    >>> getting monthly/quarterly reports downloaded)

    >>
    >> Use the VALUE() function:
    >>
    >> =SUM(VALUE(X:X))
    >>

    > Thanks GS, also found I can manually click the error window after selecting
    > bogus cells. Will look to vba it.
    > Thanks
    > mark


    Ok, so you're saying that not all cells will contain numbers, ergo some
    may have text. In that case my formula won't work if any cells in the
    range contain non-numeric characters. VBA will certainly do this...

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 26, 2012
    #6
  7. mp

    mp Guest

    On 4/25/2012 6:32 PM, GS wrote:
    > mp has brought this to us :
    >> On 4/25/2012 5:12 PM, GS wrote:
    >>> mp wrote on 4/25/2012 :
    >>>> I know someone is going to say, just google it ;-) which I have and am
    >>>> sure it's there but I don't see it. Used to be a way in 97' to convert
    >>>> format of a cell from text to number
    >>>>
    >>>> (cells have existing values in it- xls downloaded from a site- numbers
    >>>> come in as text so I can't SUM(x:x) etc.
    >>>>
    >>>> Have searched Help and google but can't find using "convert text to
    >>>> number" and many variations of that...sure i'm missing the obvious,
    >>>> sorry
    >>>> Thanks
    >>>> Mark
    >>>>
    >>>> ps would then try to find a way to automate with vba (repeated task
    >>>> getting monthly/quarterly reports downloaded)
    >>>
    >>> Use the VALUE() function:
    >>>
    >>> =SUM(VALUE(X:X))
    >>>

    >> Thanks GS, also found I can manually click the error window after
    >> selecting bogus cells. Will look to vba it.
    >> Thanks
    >> mark

    >
    > Ok, so you're saying that not all cells will contain numbers, ergo some
    > may have text. In that case my formula won't work if any cells in the
    > range contain non-numeric characters. VBA will certainly do this...
    >


    got it, thanks
     
    mp, Apr 26, 2012
    #7
  8. mp

    Zaidy036 Guest

    Zaidy036, Apr 27, 2012
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jeff Muniz

    Rounding time values to nearest 15 minute and convert to decimal

    Jeff Muniz, Jul 7, 2003, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    933
    J.E. McGimpsey
    Jul 7, 2003
  2. Murtaza

    How can I Convert Links into Values for only Visible Cells?

    Murtaza, Mar 3, 2004, in forum: Microsoft Excel Discussion
    Replies:
    4
    Views:
    965
    Murtaza
    Mar 3, 2004
  3. Takeadoe

    Convert Hard-coded cell values to constants

    Takeadoe, May 19, 2006, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    150
    RagDyeR
    May 19, 2006
  4. Eanna

    paste AND convert from delimited values

    Eanna, Jun 29, 2006, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    127
    Dave Peterson
    Jun 29, 2006
  5. Peter

    Convert formula imnto values

    Peter, Sep 28, 2006, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    130
    Pete_UK
    Sep 28, 2006
Loading...

Share This Page