| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing zeros from data list
I have a giant list in one column full of data that is prefixed by zeros.
eg. 271 is represented as 00271, 274 as 00274 I am trying to remove these zeros so as to be able to merge and sort this data with another set. Can anyone tell me how to remove these zeros from the data without manually typing them in? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing zeros from data list
Hi
How is the column currently formatted? Is it Custom00000? If so, change the format to Custom000 If it is Text, then copy a blank cellmark the range of your dataPaste SpecialAdd. This should change you values from Text to Numeric. -- Regards Roger Govier "mokihi" wrote in message ... I have a giant list in one column full of data that is prefixed by zeros. eg. 271 is represented as 00271, 274 as 00274 I am trying to remove these zeros so as to be able to merge and sort this data with another set. Can anyone tell me how to remove these zeros from the data without manually typing them in? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing zeros from data list
mokihi wrote:
I have a giant list in one column full of data that is prefixed by zeros. eg. 271 is represented as 00271, 274 as 00274 I am trying to remove these zeros so as to be able to merge and sort this data with another set. Can anyone tell me how to remove these zeros from the data without manually typing them in? It looks like the cells contain text. Put a zero in an empty cell somewhere and then copy it. Select your column and Edit / Paste Special / Values / Add. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing zeros from data list
enter the number 1 in any empty cell and copy that cell,
now select cells which has prefixed with zero's | go to edit | pastespecial | click multiply | ok On Dec 10, 1:52*am, mokihi wrote: I have a giant list in one column full of data that is prefixed by zeros. eg. 271 is represented as 00271, 274 as 00274 I am trying to remove these zeros so as to be able to merge and sort this data with another set. Can anyone tell me how to remove these zeros from the data without manually typing them in? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing zeros from data list
Sometimes this will remove leading zeros from TEXT numbers and convert them
to numeric numbers. Select the range in question Goto the menu DataText to Columns Click Next twice In step 3 make sure Column data format is set to GENERAL Click Finish -- Biff Microsoft Excel MVP "mokihi" wrote in message ... I have a giant list in one column full of data that is prefixed by zeros. eg. 271 is represented as 00271, 274 as 00274 I am trying to remove these zeros so as to be able to merge and sort this data with another set. Can anyone tell me how to remove these zeros from the data without manually typing them in? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop automatically removing leading zeros? | Excel Discussion (Misc queries) | |||
| removing duplicate data from a single list | Excel Worksheet Functions | |||
| removing zeros from fromulas in excel | Excel Discussion (Misc queries) | |||
| Removing a list of data from a larger one | Excel Discussion (Misc queries) | |||
| How do I stop excel removing zeros? | Excel Discussion (Misc queries) | |||
Hybrid Mode
