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 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

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" :-)

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

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

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

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

<snip>

free ASAP Utilities has this function
http://www.asap-utilities.com/