| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Did not see any reply from yesterday. Need data formatting help
Ok I have data in excel that looks like this:
2345 23667867 12345678 8564 3557 The data needs to be formatted to look like this 00002345 23667867 12345678 00008564 00003557 How do I add the zero to eacfh of the fields? Does this entail scripting of some sort? Please reply!!! ASAP!!! |
|
#2
|
|||
|
|||
|
Hi TechGuyatwork
Format the cells like this Select them Ctrl-1 Custom, use 00000000 -- Regards Ron de Bruin http://www.rondebruin.nl "TechGuyatwork" wrote in message ... Ok I have data in excel that looks like this: 2345 23667867 12345678 8564 3557 The data needs to be formatted to look like this 00002345 23667867 12345678 00008564 00003557 How do I add the zero to eacfh of the fields? Does this entail scripting of some sort? Please reply!!! ASAP!!! |
|
#3
|
|||
|
|||
|
Try this:
FormatCellsNumber Category: Custon Type: 00000000 Click [OK] Note: that won't actuall prepend zeros to the the numbers. It will just display zeros where needed. Does that help? -- Regards, Ron |
|
#4
|
|||
|
|||
|
Hello
Here is a quick fix with a caveat... Suppose data start in cell A1 and goes down column 1...type in cell B1... =IF(LEN(A1)<8,"0000"&A1,A1) And then drag this formula down. Caveats... (1)In your example I assume you either have data of 4 digits or 8 digits in length. In the formula, the first part assesses whether the data is less then 8 digits. If so it adds the 0000, otherwise the data remains as it is. (2) By adding "0000" you are adding a string to the data and so the values produced are no longer of number format. If you try to type 00001234 in Excel then you just get 1234 as a number format. To get around this I have made the data into a string format. You will notice this as those values will be aligned leff in the cell but others that have not had 0000 added will be aligned right. The result of this is that it may affect any calculations you may want to do using the data. Hence this is a quick and dirty fix...for aesthetics only... Alex "TechGuyatwork" wrote: Ok I have data in excel that looks like this: 2345 23667867 12345678 8564 3557 The data needs to be formatted to look like this 00002345 23667867 12345678 00008564 00003557 How do I add the zero to eacfh of the fields? Does this entail scripting of some sort? Please reply!!! ASAP!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to search for and display data in another worksheet | Excel Worksheet Functions | |||
| Import Data Keeps asking for Password | Excel Discussion (Misc queries) | |||
| Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) | |||
| Line Graph Data Recognition | Charts and Charting in Excel | |||
| Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
Linear Mode
