Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
numeric value is not formatting correctly
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 "," 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 http://www.officekb.com |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
numeric value is not formatting correctly
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. "Fam via OfficeKB.com" <u18245@uwe wrote in message news:5b48a496d791f@uwe... 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 "," 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 http://www.officekb.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
numeric value is not formatting correctly
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 "Fam via OfficeKB.com" <u18245@uwe wrote in message news:5b48a496d791f@uwe... 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 "," 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 http://www.officekb.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
numeric value is not formatting correctly
Andy, thanks for the reponse.
I did not get it. Can you please eloborate little more? Thanks wrote: 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. Hello everybody, I have one small problem. Hope someone can shed some light on. [quoted text clipped - 12 lines] Any help will be greatly appreciated. Fam -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
numeric value is not formatting correctly
Thank you guys.
It worked perfectly. Bernard Liengme wrote: 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 Hello everybody, I have one small problem. Hope someone can shed some light on. [quoted text clipped - 12 lines] Any help will be greatly appreciated. Fam -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Formatting web imported data correctly | Excel Worksheet Functions | |||
Match Each Numeric occurrence and Return Individual Rows of Data | Excel Worksheet Functions | |||
Numeric formatting problem | Excel Worksheet Functions |