Displaying phone numbers as numbers

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:25 PM GMT

Question

I want to display (111)111-1111 as 1111111111 Is there any formula to display numbers without parentheses and hyphens.I want to display telephone numbers as numbers


Answer

Here it is two probabilities.

First Probability:- The actual value of the cell may be 1111111111 and the format of the cell may be (000)000-0000. You can just check it whether the numbers are Right Aligned in the cells. If the numbers are in right side of the cell then the value is 1111111111 only and it is formatted using the custom format. Because real numbers will always be in right side of the cell. Another method to check is that if you place the cursor in the cell and look in formula bar then it will show the value as 1111111111. If it so then just select the cells and Do right click>>Format cells>>Number>>Category>>General and give Ok.

Second Probability:- If the number is showing in the left side of the cells and in the formula bar it is showing the value as (111)111-1111 instead of 1111111111 then it is Text Values.

I assume that you are having the value (111)111-1111 in A1 cell

A1 cell (111)111-1111

Copy and paste the below value in B1 cell. =IF(A1="","",VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ","")))

Change the cell reference A1 in the above formula to your desired cell, if required.

Advertisement

©2021 eLuminary LLC. All rights reserved.