In this pose we will see how could we insert hyphen within a phone number in excel.
With the evolution of technology, there have been multiple databases and formats to store the phone number and mobile number in the system and they all store the phone numbers in different formats.
With the help of this pot, I would like to demonstrate how we could insert hyphen in between phone numbers or mobile numbers to justify your requirement as phone number in the format xxx-xxx-xxxx is the most popular format and sometimes we don’t get the numbers in this format.
Below is the example of what we are talking about as you can see in column A, we have phone number/mobile number without the hyphens in between them and in column B, we have inserted hyphens or dashes within the phone numbers and mobile numbers to make it formatted.
We will embed hyphens in a phone number string with the help of LEFT, RIGHT and MID function in excel.
To insert hyphen within a phone number of 10 digit and make it like xxx-xxx-xxxx, enter the formula
=LEFT(A2,3)&”-“&MID(A2,4,3)&”-“&RIGHT(A2,4) in cell B2 as shown in the pic below.
Our formula is extracting substring from the main string and inserting hyphen at the places with the help of “&” concatenation operator.
I will explain the entire formula below.
LEFT(A2,3)-is extracting the first 3 digits from the number.
“-“&MID(A2,4,3)-concatenating “-“ after the first 3 digits and concatenating it with the next 3 digits, MID formula is extracting the next 3 digits.
&”-“&RIGHT(A2,4)- extracting the last 4 digits from the phone number and concatenating with rest of the phone numbers and a hyphen.
You can see the result after applying the formula as shown below.
Now you drag the formula down to insert hyphen in all the phone numbers that you have.
So with the help of text functions in excel we have seen how to insert hyphens, but you can modify the formula to insert any character/special character that you want to insert in between the phone numbers.
Hope this helped.
