PC Review


Reply
Thread Tools Rate Thread

convert text to values

 
 
mp
Guest
Posts: n/a
 
      25th Apr 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)
 
Reply With Quote
 
 
 
 
GS
Guest
Posts: n/a
 
      25th Apr 2012
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


 
Reply With Quote
 
 
 
 
mp
Guest
Posts: n/a
 
      25th Apr 2012
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" :-)
 
Reply With Quote
 
mp
Guest
Posts: n/a
 
      25th Apr 2012
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

 
Reply With Quote
 
GS
Guest
Posts: n/a
 
      25th Apr 2012
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


 
Reply With Quote
 
GS
Guest
Posts: n/a
 
      26th Apr 2012
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


 
Reply With Quote
 
mp
Guest
Posts: n/a
 
      26th Apr 2012
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

 
Reply With Quote
 
Zaidy036
Guest
Posts: n/a
 
      27th Apr 2012
<snip>

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

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert DropDownList values to enum values Andy B. Microsoft ASP .NET 2 8th Jun 2009 08:05 PM
Convert values in a variant array to integer values Graham McNeill Microsoft Excel Programming 1 13th Nov 2004 01:47 AM
How to Convert Numeric Values into Text Values with a Formula Vijay Microsoft Excel Worksheet Functions 3 10th Nov 2003 04:12 PM
Adding leading zero to convert one digit hex values like 0,1,2,A in to two 2 digit hex values like 01,02,0A Rich Microsoft Access Queries 1 22nd Sep 2003 10:34 PM
convert hex-values to decimal values Markus Früh Microsoft Excel Programming 6 28th Aug 2003 10:32 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:08 AM.