software

Hello everybody,
I have one small problem. Hope someone can shed some light on.
I'm using =LEFT(A2,5) fuction to extract some numeric values. Once the value
is extracted in different cell (let say C2), the value in C2 is not
formatting correctly. And what I mean by that is that the $ sign or quot;,quot; is
not applicable to format the extracted value. Yes, once you over write it, it
will format like $ 23.00 but you have to overwite it. Now I have atleast over
a thousand rows and it will take lot of time if I have to do cell by cell. Is
there is way that I can highlight the extracted values and somehow able to
format correctly?
Any help will be greatly appreciated.
Fam

--
Message posted via www.officekb.com

Hi

Try this. Copy an empty, unused cell. Select the range of 'numbers' and then
Edit/Paste Special/Add
Make a backup before you start!

Hope this helps.
Andy.

quot;Fam via OfficeKB.comquot; lt;u18245@uwegt; wrote in message
news:5b48a496d791f@uwe...
gt; Hello everybody,
gt; I have one small problem. Hope someone can shed some light on.
gt; I'm using =LEFT(A2,5) fuction to extract some numeric values. Once the
gt; value
gt; is extracted in different cell (let say C2), the value in C2 is not
gt; formatting correctly. And what I mean by that is that the $ sign or quot;,quot; is
gt; not applicable to format the extracted value. Yes, once you over write it,
gt; it
gt; will format like $ 23.00 but you have to overwite it. Now I have atleast
gt; over
gt; a thousand rows and it will take lot of time if I have to do cell by cell.
gt; Is
gt; there is way that I can highlight the extracted values and somehow able to
gt; format correctly?
gt; Any help will be greatly appreciated.
gt; Fam
gt;
gt; --
gt; Message posted via www.officekb.com
That is because the LEFT function returns text: you may have 23.00 but it is
still text made up of digits.
To coerce a numeric value you need to 'do some math' on the result. So any
of these work:
=--LEFT(A2,5) 'preferred
=LEFT(A2,5)*1
=LEFT(A2,5) 0
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Fam via OfficeKB.comquot; lt;u18245@uwegt; wrote in message
news:5b48a496d791f@uwe...
gt; Hello everybody,
gt; I have one small problem. Hope someone can shed some light on.
gt; I'm using =LEFT(A2,5) fuction to extract some numeric values. Once the
gt; value
gt; is extracted in different cell (let say C2), the value in C2 is not
gt; formatting correctly. And what I mean by that is that the $ sign or quot;,quot; is
gt; not applicable to format the extracted value. Yes, once you over write it,
gt; it
gt; will format like $ 23.00 but you have to overwite it. Now I have atleast
gt; over
gt; a thousand rows and it will take lot of time if I have to do cell by cell.
gt; Is
gt; there is way that I can highlight the extracted values and somehow able to
gt; format correctly?
gt; Any help will be greatly appreciated.
gt; Fam
gt;
gt; --
gt; Message posted via www.officekb.com
Andy, thanks for the reponse.
I did not get it. Can you please eloborate little more?
Thanks

wrote:
gt;Hi
gt;
gt;Try this. Copy an empty, unused cell. Select the range of 'numbers' and then
gt;Edit/Paste Special/Add
gt;Make a backup before you start!
gt;
gt;Hope this helps.
gt;Andy.
gt;
gt;gt; Hello everybody,
gt;gt; I have one small problem. Hope someone can shed some light on.
gt;[quoted text clipped - 12 lines]
gt;gt; Any help will be greatly appreciated.
gt;gt; Fam

--
Message posted via www.officekb.com

Thank you guys.
It worked perfectly.

Bernard Liengme wrote:
gt;That is because the LEFT function returns text: you may have 23.00 but it is
gt;still text made up of digits.
gt;To coerce a numeric value you need to 'do some math' on the result. So any
gt;of these work:
gt;=--LEFT(A2,5) 'preferred
gt;=LEFT(A2,5)*1
gt;=LEFT(A2,5) 0
gt;best wishes
gt;gt; Hello everybody,
gt;gt; I have one small problem. Hope someone can shed some light on.
gt;[quoted text clipped - 12 lines]
gt;gt; Any help will be greatly appreciated.
gt;gt; Fam

--
Message posted via www.officekb.com