We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

phone numbers in exchange

Gareth McKee
Gareth McKee asked
on
Medium Priority
221 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Commented:
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?
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Or save it as .xls of course. ;)

Gareth McKeeCEO/Owner

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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.

Commented:
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.
Gareth McKeeCEO/Owner

Author

Commented:
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.

Commented:
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
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
Information Services Manager
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
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.

Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
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".
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.