Forum Discussion
Formatting phone numbers
Hi,
When I download data from either the back end of our telephony system or our CRM, the telephone numbers come out like this. My excel doesn't show me any options when looking at special formatting nor can I work out how to create a custom setting. So, who can please help me with what I need to do so that it reads as a correct format i.e. the 1st one should read as 01892521841? I'm looking to do this so that I can run some analysis between the different data sets.
| 1892521841 |
| 4.41E+11 |
| 4.41E+11 |
| 1323842119 |
| 4.42E+11 |
| 4.41E+11 |
| 1273483119 |
| 4.43E+11 |
| 4.41E+11 |
| 4.42E+11 |
| 1342811777 |
4.42E+11 |
For display purposes, you might be able to use the custom format string "00000000000" (without the quote characters):
(Once I did that, Excel thought I was using a Polish format. Upon closing and reopening the Format Cells dialog, I saw the following.)
You can even include additional characters, such as spaces (normal or thin) and/or + - ( ) if those are appropriate:
and other characters can also be included by surrounding them with quote characters within the format string.
But your sample data includes both 11-digit (you say) numbers and 12-digit numbers (as is obvious from the scientific notation values). That could be a complication. If both require a leading zero, you might use a custom format like "[>99999999999]0000000000000;00000000000" (again, without the quote characters), which effectively says "if the number has more than 11 digits (ignoring leading zeros), format it to use (at least) 13 digits, adding a leading zero if needed; otherwise format it to use 12 digits, adding a leading zero if needed".
If none of those work for you, please be more specific on the possible layouts of the phone numbers in your data, and your formatting requirements. And mention which version of Excel you are using, on which platform.
As for the "analysis between the different data sets", that makes it appear as though you "need" these numeric values as text. If so, you might import the data as Text (described in the earlier reply) or consider reconversion with a formula using the TEXT function.
It seems like Excel is treating your phone numbers as numbers in scientific notation due to their format. To format them as regular phone numbers, you can follow these steps:
- Select the cells containing the phone numbers that you want to format.
- Right-click on the selected cells and choose "Format Cells."
- In the "Format Cells" dialog box, go to the "Number" tab.
- In the left panel, select "Number" as the category.
- In the right panel, you can choose the number of decimal places to display. Since phone numbers should not have decimal places, set this to "0."
- You can also specify the thousands separator if you prefer, but this is optional.
- Click "OK" to apply the formatting.
Your phone numbers should now be displayed in a regular numeric format without scientific notation.
If you encounter issues where Excel still displays numbers in scientific notation, it is possible that the data source is exporting them as text, but Excel is interpreting them as numbers. In such cases, you can try the following steps:
- Format the cells as text before importing the data:
- Before importing the data into Excel, format the cells where you expect phone numbers to be as "Text" in Excel. To do this, select the columns or cells, right-click, choose "Format Cells," and then select the "Text" category.
- After formatting the cells as text, import the data into those cells.
- Use a leading single quotation mark ('0) before the phone numbers:
- When entering or importing phone numbers into Excel, use a single quotation mark followed by the number to indicate that its text and should not be treated as a number. For example, '01892521841.
- Excel will treat this as text, and you can then format it as needed without encountering scientific notation.
These steps should help you display your phone numbers correctly in Excel.
The text and steps were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
- Thanks NikolinoDE for tsking the time to produce this response
For display purposes, you might be able to use the custom format string "00000000000" (without the quote characters):
(Once I did that, Excel thought I was using a Polish format. Upon closing and reopening the Format Cells dialog, I saw the following.)
You can even include additional characters, such as spaces (normal or thin) and/or + - ( ) if those are appropriate:
and other characters can also be included by surrounding them with quote characters within the format string.
But your sample data includes both 11-digit (you say) numbers and 12-digit numbers (as is obvious from the scientific notation values). That could be a complication. If both require a leading zero, you might use a custom format like "[>99999999999]0000000000000;00000000000" (again, without the quote characters), which effectively says "if the number has more than 11 digits (ignoring leading zeros), format it to use (at least) 13 digits, adding a leading zero if needed; otherwise format it to use 12 digits, adding a leading zero if needed".
If none of those work for you, please be more specific on the possible layouts of the phone numbers in your data, and your formatting requirements. And mention which version of Excel you are using, on which platform.
As for the "analysis between the different data sets", that makes it appear as though you "need" these numeric values as text. If so, you might import the data as Text (described in the earlier reply) or consider reconversion with a formula using the TEXT function.
- Thank you very much - that has solved the differing number lengths for me
