convert text to values

[mp]

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)

 

[GS]

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

 

[mp]

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]

mp wrote on 4/25/2012 :

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

 

[GS]

After serious thinking mp wrote :

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]

mp has brought this to us :

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

 

[mp]

mp has brought this to us :

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

 

[Zaidy036]

<snip>

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