Gareth McKee
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
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
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?
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
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. ;)
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
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.
If none of these work you may have to use a text editor. I often use Notepad++ for editing my csv files.
ASKER
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.
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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".
... 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".