Link to home
Start Free TrialLog in
Avatar of Gareth McKee
Gareth McKeeFlag for Canada

asked on

phone numbers in exchange

hi all,

I have a spread sheet (csv) with *,000s of phone numbers in, currently they are showing without the initial '0'.

I can  highlight the column, select format and custom and enter 00000000000 which will add the 0 to the front of the number. however when saved and reopened the 0 disappears again.

even if I save as text afterwards the 0 disappears.

the aim - to permanently add the 0 to the front of the phone numbers.

any help is gratefully received.
thanks
gareth
Avatar of qbakies
qbakies
Flag of United States of America image

What if you just format the cells as text instead of custom?  What version of excel are you using?  Can you open and edit in a text editor instead of excel?
Avatar of Rob Henson
Using the Custom format doesn't actually change the data, it only affects how it appears.

When saving as CSV or TXT it is only saving raw data.

To get the data to change you would have to use a formula like:

=TEXT(A1,"00000000000")    

Assumes the number in cell A1. This would change the data and would need copying down the relevant number of rows. You could then Copy the result and Paste it back over the original data with Paste Special > Values. The leading zeros should then stay.

Cheers
Rob H
Or save it as .xls of course. ;)

Avatar of Gareth McKee

ASKER

thanks guys

Rorya - need to be a csv as it is being imported into a 3rd party  application
qbakies - this works fine for xls but not in csv
rob - love the idea but again once saved to a csv file the 0 is removed.

any other ideas?
thanks
gareth
The 0 will get removed only when you open in Excel using Rob's method - if you actually view the csv in Notepad for example they should still be there.
What if you save it as a xls to keep the leading 0 and then save that correct xls as csv.

If none of these work you may have to use a text editor.  I often use Notepad++ for editing my csv files.
qbakies - no joy on the xls to csv I am afraid.

is there a way to globally put in a 0 using a text editor.
I believe there is a way in Notepad++.  You can edit vertically which may work for you.  See this article under 'Edit... Vertically!'

http://www.maximumpc.com/article/features/notepad_cheat_sheet_11_tricks_and_tips_plaintext_power_users
How annoying??? I would have thought that the conversion to text would work.

Not to be beaten, I have found a way.

Change the formula suggested above to:

=" "&TEXT(A1,"00000000000")

Now save as a PRN file (available in 2007, don't know about earlier).

When re-opening the text import wizard pops up, choose Fixed Width and position the separator to the left of the now apparent zero and import that column as text. The zero then stays.

However, can the other application import PRN files???

Cheers
Rob H
ASKER CERTIFIED SOLUTION
Avatar of Ron Malmstead
Ron Malmstead
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
btw-  When you open a new sheet...open a new instance of excel...do not simply click file > new, or your results will be continued annoyance.

Ahh...didn't see the CSV was a requirement.
... if you do the method I just told you, then save as csv, it should keep the value as it was changed.

Doing it that way is actually changing the value, not just a display "mask".