Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading Zero and the change command
My data is preceeded by "." up to "........" When I use the change command to
delete the ".", it also drops any leading zeros in the text. The field is formatted as text and the data is not proceeded by a single quote. Ex. ...01263 changes to 1263. Any ideas why this would happen? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading Zero and the change command
When you get rid of the leading full-stops and are left with 01263,
then Excel will treat this as if you typed it in - it comprises only numeric characters, therefore it must be a number, and any leading zeroes can therefore be ignored (Excel's logic). If all your numbers are to be 5 digits in length, then you could use =RIGHT(A1,5) or alternatively you could apply a custom format to the cells, but you will need to give a few more details concerning the variations in your numbers if you want a more complete answer. Hope this helps. Pete On Jun 6, 10:46 pm, mmccoog wrote: My data is preceeded by "." up to "........" When I use the change command to delete the ".", it also drops any leading zeros in the text. The field is formatted as text and the data is not proceeded by a single quote. Ex. ...01263 changes to 1263. Any ideas why this would happen? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading Zero and the change command
Thanks Pete. My data is various lengths so instead of deleting the '.', I
changed it to a single quote and that did the trick. Thanks for your response. Mary Ann "Pete_UK" wrote: When you get rid of the leading full-stops and are left with 01263, then Excel will treat this as if you typed it in - it comprises only numeric characters, therefore it must be a number, and any leading zeroes can therefore be ignored (Excel's logic). If all your numbers are to be 5 digits in length, then you could use =RIGHT(A1,5) or alternatively you could apply a custom format to the cells, but you will need to give a few more details concerning the variations in your numbers if you want a more complete answer. Hope this helps. Pete On Jun 6, 10:46 pm, mmccoog wrote: My data is preceeded by "." up to "........" When I use the change command to delete the ".", it also drops any leading zeros in the text. The field is formatted as text and the data is not proceeded by a single quote. Ex. ...01263 changes to 1263. Any ideas why this would happen? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change the face color of an Excel command button | Excel Discussion (Misc queries) | |||
How do I change font colour in an if command? | Excel Worksheet Functions | |||
CHANGE DATA SERIES WITH A COMMAND BUTTON | Charts and Charting in Excel | |||
Change Name of Command Button | Excel Discussion (Misc queries) | |||
use replace command to change the contents of formulas | Excel Discussion (Misc queries) |