Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
cleaning numbers
i'm cleaning up some data to transfer from a spreadsheet to Access and this
is bugging me how do i clean up the formatting of a phone number field without Excel removing the starting 0 from a phone number? i've changed the format to text but it still deletes the 0 - aaarrrggg! -- deb |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
cleaning numbers
I usually let excel drop the leading 0's, but then I give the cells a custom
format so that it shows again. This doesn't change the value, but it does change the way it looks. Is that ok? If not, maybe you can clean up your data and then use a helper column with a formula like: =text(a1,"0000000000") Drag down as far as you need, convert it to values and delete the original column. deb wrote: i'm cleaning up some data to transfer from a spreadsheet to Access and this is bugging me how do i clean up the formatting of a phone number field without Excel removing the starting 0 from a phone number? i've changed the format to text but it still deletes the 0 - aaarrrggg! -- deb -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
cleaning numbers
yep, that's useful - thanks
now i just have to deal with the fact they've mixed mobile numbers, quick dial numbers, international numbers, and notes! oh and just for the hell of it they have some numbers with the area code, some not and some with the +61 prefix why me lord? -- deb "Dave Peterson" wrote: I usually let excel drop the leading 0's, but then I give the cells a custom format so that it shows again. This doesn't change the value, but it does change the way it looks. Is that ok? If not, maybe you can clean up your data and then use a helper column with a formula like: =text(a1,"0000000000") Drag down as far as you need, convert it to values and delete the original column. deb wrote: i'm cleaning up some data to transfer from a spreadsheet to Access and this is bugging me how do i clean up the formatting of a phone number field without Excel removing the starting 0 from a phone number? i've changed the format to text but it still deletes the 0 - aaarrrggg! -- deb -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Cleaning | Excel Discussion (Misc queries) | |||
Cleaning forms | Excel Discussion (Misc queries) | |||
Cleaning data | Excel Worksheet Functions | |||
Cleaning Data | Excel Discussion (Misc queries) | |||
Spreadsheet cleaning | Excel Discussion (Misc queries) |