How to separate area code from phone number in Excel?
If you have a list of clients' phone numbers in a worksheet, and you want to divide them based on their areas, you need to separate the area codes from their phone numbers first. Now, I tell you two quick ways to separate the area codes from phone numbers in Excel.
Separate area code from phone number with formula in Excel
Separate area code from phone number with Text to column
Separate area code from phone number with formula in Excel
In general, the area code is the first three numbers of the phone number, so you can use the follow formula:
Select a cell and type this formula= MID(B1,1,3) ( B1 is the cell you want to separate the area code from), and press Enter button, then drag fill handle to fill the range you want to apply this formula. See, the area codes are extracted from the phone numbers list:
Separate area code from phone number with Text to column in Excel
In Excel, you also can use Text to column feature to finish the separating.
1. Select the list of phone number, click Data > Text to Columns. See screenshot:
2. In the Convert Text to Columns Wizard dialog box, check Fixed width and click Next. See screenshot:
3. Then set your field width by setting the break lines with arrow. See screenshot:
4. Click Finish. Then you see the area codes are separated from the full phone numbers
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
