| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format
I have a spreadsheet with a list of Zip Codes. The Zip Codes are formatted as "#####-####". So it looks like 98507-9046 but was entered as 985079046. I want the Zip Codes to be stored as 98507-9046 instead of 985079046. How can I do this for over 1000 entries instead of doing it manually one by one? thanks, Lamar |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format
Use a help column, assume the data starts in A2, insert a new column B
(unless B is already empty) and in B2 put Copy down the formula 1000 rows (double click lower right corner of B2) copy and paste special as values in place, once that is done you can delete column A -- Regards, Peo Sjoblom "Lamar" wrote in message ... I have a spreadsheet with a list of Zip Codes. The Zip Codes are formatted as "#####-####". So it looks like 98507-9046 but was entered as 985079046. I want the Zip Codes to be stored as 98507-9046 instead of 985079046. How can I do this for over 1000 entries instead of doing it manually one by one? thanks, Lamar |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format
These formulae can convert the numerics to the text format you
requi =TEXT(A1,"00000-0000") or =LEFT(A1,5)&"-"&RIGHT(A1,4) assuming the data starts in A1. Just copy them down the column, then you can fix the values and get rid of the original column. Hope this helps. Pete On Jun 28, 11:34 pm, Lamar wrote: I have a spreadsheet with a list of Zip Codes. The Zip Codes are formatted as "#####-####". So it looks like 98507-9046 but was entered as 985079046. I want the Zip Codes to be stored as 98507-9046 instead of 985079046. How can I do this for over 1000 entries instead of doing it manually one by one? thanks, Lamar |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format
Oops! Forgot to include the formula
=TEXT(A2,"00000-0000") -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Use a help column, assume the data starts in A2, insert a new column B (unless B is already empty) and in B2 put Copy down the formula 1000 rows (double click lower right corner of B2) copy and paste special as values in place, once that is done you can delete column A -- Regards, Peo Sjoblom "Lamar" wrote in message ... I have a spreadsheet with a list of Zip Codes. The Zip Codes are formatted as "#####-####". So it looks like 98507-9046 but was entered as 985079046. I want the Zip Codes to be stored as 98507-9046 instead of 985079046. How can I do this for over 1000 entries instead of doing it manually one by one? thanks, Lamar |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format
custom format:
00000-0000 "Lamar" wrote: I have a spreadsheet with a list of Zip Codes. The Zip Codes are formatted as "#####-####". So it looks like 98507-9046 but was entered as 985079046. I want the Zip Codes to be stored as 98507-9046 instead of 985079046. How can I do this for over 1000 entries instead of doing it manually one by one? thanks, Lamar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
| Decide comment format 'globally'? Restore format with ws_change? | Excel Discussion (Misc queries) | |||
| Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
| Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
| how to format excel format to text format with separator "|" in s. | New Users to Excel | |||
Linear Mode
