Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I convert 999999 to 999-999 in an excel document?
I have copyied a large number of parcel numbers into excel (maybe 3,000) and
I need to change the format in order to use them in another application. For instance: 999999 needs to become 999-999 OR 99999 needs to become 099999. How can this be done without changing each entry manually. Thank you. |
#2
|
|||
|
|||
Hi Chris
if the numbers could be either 6 characters long or something less than 6 at one time (ie you don't do a batch of 6 digit numbers and then a batch of 5 and then a batch of 4) then one way is to use a helper column (ie another column adjacent to your numbers) with the following formula =IF(LEN(A1)=6,LEFT(A1,3)&"-"&RIGHT(A1,3),TEXT(A1,"000000")) you can then copy / edit - paste special - values this over the original data and delete the helper column. (note, this will change the numbers into text) If you do a worksheet of 6 digit numbers then you could select the cells use format / cells - custom and type ###-### and click OK if you then had a worksheet of 5 digit numbers you could select them and use format / cells - custom and type 000000 and click OK as these two options are only formatting options the numbers will remain numbers. hope this helps Cheers JulieD "Chris" wrote in message ... I have copyied a large number of parcel numbers into excel (maybe 3,000) and I need to change the format in order to use them in another application. For instance: 999999 needs to become 999-999 OR 99999 needs to become 099999. How can this be done without changing each entry manually. Thank you. |
#3
|
|||
|
|||
On Thu, 6 Jan 2005 07:43:04 -0800, "Chris"
wrote: I have copyied a large number of parcel numbers into excel (maybe 3,000) and I need to change the format in order to use them in another application. For instance: 999999 needs to become 999-999 OR 99999 needs to become 099999. How can this be done without changing each entry manually. Thank you. If you want a text string that you can then import into another application, in an adjacent column put a formula of the type: =TEXT(A1,"[99999]000-000;000000") Change A1 to reflect the first cell of your parcel number list. If you only want to change the FORMAT in Excel (without changing the underlying number, then select the cells and do: Format/Cells/Number Custom Type: [99999]000-000;000000 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Help, insert a word document contents into excel tab? | Excel Discussion (Misc queries) | |||
how do i convert a word document to excel | Excel Discussion (Misc queries) | |||
getting specific info from a word document into excel | Excel Discussion (Misc queries) | |||
HOW DO I CONVERT SCANNED DOCUMENT IN PDF FORMAT BACK TO AN EXCEL. | Excel Discussion (Misc queries) |