Formatting phone number in Excel
Solved
Jona
-
Hasdrubal -
Hasdrubal -
Hello everyone,
I am supposed to create an Excel table with the list of our clients
(Last name, first name, private phone number, professional number, mobile number)
in order to use this table to enter all the contacts into our company's new phone in the format 0012345678 !!
My problem is as follows: all the phone numbers are formatted as follows:
e.g.: 012/34.56.78, I first used the replace function to add a 0 in front because I will need to dial the 0 first to exit the internal line. (replacement 012/ with 0012/) OK
So my numbers now look like 0012/34.56.78, then I replaced the "/" with "nothing", OK
The numbers now look like 001234.56.78
My new problem is that when I try to delete these ".", the 00 disappears even though it is in text format !!
And I don't want to manually insert the 00 in front for each client (+ more than 1200!!)
Is there a simple manipulation or formula that would allow me to add back the missing 00?
I am supposed to create an Excel table with the list of our clients
(Last name, first name, private phone number, professional number, mobile number)
in order to use this table to enter all the contacts into our company's new phone in the format 0012345678 !!
My problem is as follows: all the phone numbers are formatted as follows:
e.g.: 012/34.56.78, I first used the replace function to add a 0 in front because I will need to dial the 0 first to exit the internal line. (replacement 012/ with 0012/) OK
So my numbers now look like 0012/34.56.78, then I replaced the "/" with "nothing", OK
The numbers now look like 001234.56.78
My new problem is that when I try to delete these ".", the 00 disappears even though it is in text format !!
And I don't want to manually insert the 00 in front for each client (+ more than 1200!!)
Is there a simple manipulation or formula that would allow me to add back the missing 00?
Configuration: Windows XP Internet Explorer 7.0
4 réponses
Hello,
Another solution is to go through numbers.
Replace / with nothing
Replace . (the period) with nothing: for this, you need to type ~. instead of . (tilde + period instead of period)
You then get a number (placed on the right of the cell).
All you need to do is choose a custom format 00#" "##" "##" "##" "##
Another solution is to go through numbers.
Replace / with nothing
Replace . (the period) with nothing: for this, you need to type ~. instead of . (tilde + period instead of period)
You then get a number (placed on the right of the cell).
All you need to do is choose a custom format 00#" "##" "##" "##" "##
I consulted other forums and apparently, it was possible to use the command
A2 =CONCATENATE("00",A1).
So, after removing the ".", I changed the cell format to text format, then used this formula.
Thank you all for your help.