Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formating a number
I have been provided a list of phone numbers in an excel spreadsheet. I am
trying to add a space in between the code and the remaining number eg: 0412346567 becomes 0412 346567 I would usually go to format, custom and enter #### ######. But after closer inspection, all the numbers have ' before them (although it does not show in the cell). So I am unable to format them using the usual method. Is there any way I can do this. Excel Help said that if I go to Tools, Options, Transitions, and untick "Transitions Navigations Keys", then the apostrophies would be removed. However, it is already unticked! |
#2
|
|||
|
|||
Hi!
Here's one solution if the lenght of these "numbers" are all the same. In the adjacent column enter this formula and copy down as needed: =LEFT(A3,4)&" "&RIGHT(A3,6) Now, select the entire range of values in that adjacent column and do Copy/Paste Special/Values. Then you can delete the original list. You could also use a custom format like you thought but do all of the numbers start with a leading zero? If so, first, you need to convert the text numbers to numeric numbers. Select an empty cell and copy it. Select the range of text numbers and do Paste Special/Add. Then you use a format like 0### ######. Biff -----Original Message----- I have been provided a list of phone numbers in an excel spreadsheet. I am trying to add a space in between the code and the remaining number eg: 0412346567 becomes 0412 346567 I would usually go to format, custom and enter #### ######. But after closer inspection, all the numbers have ' before them (although it does not show in the cell). So I am unable to format them using the usual method. Is there any way I can do this. Excel Help said that if I go to Tools, Options, Transitions, and untick "Transitions Navigations Keys", then the apostrophies would be removed. However, it is already unticked! . |
#3
|
|||
|
|||
Can you not EditReplace the ' with nothing just to leave you the numbers?
"Tray" wrote: I have been provided a list of phone numbers in an excel spreadsheet. I am trying to add a space in between the code and the remaining number eg: 0412346567 becomes 0412 346567 I would usually go to format, custom and enter #### ######. But after closer inspection, all the numbers have ' before them (although it does not show in the cell). So I am unable to format them using the usual method. Is there any way I can do this. Excel Help said that if I go to Tools, Options, Transitions, and untick "Transitions Navigations Keys", then the apostrophies would be removed. However, it is already unticked! |
#4
|
|||
|
|||
On Wed, 2 Feb 2005 19:03:06 -0800, "Tray"
wrote: I have been provided a list of phone numbers in an excel spreadsheet. I am trying to add a space in between the code and the remaining number eg: 0412346567 becomes 0412 346567 I would usually go to format, custom and enter #### ######. But after closer inspection, all the numbers have ' before them (although it does not show in the cell). So I am unable to format them using the usual method. Is there any way I can do this. Excel Help said that if I go to Tools, Options, Transitions, and untick "Transitions Navigations Keys", then the apostrophies would be removed. However, it is already unticked! What version of Excel? When you say "close inspection" are you looking at the entry in the Cell, or are you looking at the entry in the Formula Bar? If the former, then Edit/Replace ' is how to start. If the latter, then the numbers are stored as TExt and need to be converted to numbers. To do so: 1. Select a blank cell. 2. Edit/Copy 3. Select your phone numbers. 4. Edit/Paste Special/Operation Add/<OK <Esc You may then format the numbers as above. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
Defining a number in a cell by text then subtracting it by the tex | Excel Worksheet Functions | |||
multiply by actual number in cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |