cleaning numbers

[deb]

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!

 

[Dave Peterson]

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]

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?