How to separate area code from phone number in Excel?
If you have a list of clients' phone numbers in an Excel worksheet and want to analyze or group clients based on geographical regions, separating area codes from their full phone numbers becomes essential. This is especially useful when you need to filter, sort, or summarize your data according to area codes for tasks such as marketing, logistics, or regional reporting. Excel offers several effective solutions to extract area codes, and selecting the right method depends on your dataset structure and personal preference. Below, we introduce three practical methods for separating area codes from phone numbers in Excel, each with their own advantages and applicable scenarios.
Separate area code from phone number with formula in Excel
Separate area code from phone number with Text to column
VBA Code – Automatically extract area codes from phone numbers
Separate area code from phone number with formula in Excel
This method is ideal when your phone numbers consistently follow the same pattern, such as standard North American phone numbers where the area code is always the first three digits. Using a formula is quick, easy to apply, and works well for small to moderately sized lists.
Typically, the area code is represented by the first three characters in the phone number string—whether or not there are dashes or parentheses present. For basic cases where each number starts with the area code, you can use the following formula:
1. Select the cell where you want the extracted area code to appear (for example, C1 if your phone number is in B1). Enter the following formula:
=MID(B1,1,3)2. Press Enter to confirm, and the area code will appear. To quickly apply this to your entire list, drag the fill handle in the bottom-right corner of the cell down the column for all corresponding phone numbers.
Note: If your phone numbers contain formatting like spaces, dashes, or parentheses (e.g., "(123)456-7890" or "123-456-7890"), the formula may need to be adjusted to accommodate these formats. Here’s an example formula for phone numbers with parentheses:
=MID(B1,2,3)You can modify the starting position and length to match your specific data format. Always check a few samples to ensure the formula returns only the intended area code. Mixing formats in your dataset may require additional logic or cleanup.
If the formula does not extract the expected area code, review the original format of your phone numbers and adjust the formula accordingly. For example, you may use functions like LEFT(), RIGHT(), or SEARCH() for more complex data patterns.
Separate area code from phone number with Text to column in Excel
This method is useful when dealing with uniform-length phone numbers or when the area code and the rest of the number are always in the same positions. The Text to Columns tool helps split data based on fixed width or using a specified delimiter. It is convenient for medium-sized datasets, but not recommended for highly variable phone number formats or extensive data cleaning.
1. Select the column containing your list of phone numbers. Navigate to the top ribbon and click Data > Text to Columns. See screenshot:
2. In the Convert Text to Columns Wizard, choose Fixed width and click Next. This option is suitable when the area code is always the same length at the beginning of the number. See screenshot:
3. On the next screen, set your field width by clicking to place break lines after the area code (for a 3-digit area code, click after the third character). If needed, adjust or move the break line to match your data’s format. See screenshot:
If your numbers contain varying formatting or lengths, consider using the Delimited option and a specific delimiter, such as a space, dash, or parentheses.
4. Click Finish. The column is now split, and the area codes are separated into a new column, making it easier to analyze or group by area code.
Tip: Always review the preview in the wizard before finalizing. If the data looks incorrect, go back and adjust the break line or delimiter settings. Saving a backup copy of your sheet before using Text to Columns is recommended, as this feature overwrites data in adjacent columns.
VBA Code – Automatically extract area codes from phone numbers
For large datasets or lists with irregular phone number formats, writing a simple VBA macro offers a scalable way to extract area codes efficiently. This approach is especially valuable when the area codes may appear with or without parentheses, dashes, spaces, or other non-standard characters. VBA allows for customized extraction logic and handles exceptions better than formula-based methods.
1. Click Developer in the Excel ribbon, then select Visual Basic to open the VBA editor. In the new Microsoft Visual Basic for Applications window, click Insert > Module. Copy and paste the following code into the module:
Sub ExtractAreaCodes()
Dim cell As Range
Dim phoneStr As String
Dim areaCode As String
Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "\(?(\d{3})\)?[-\s.]?"
regEx.Global = False
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set phoneRange = Application.InputBox("Select phone number range", xTitleId, "", Type:=8)
For Each cell In phoneRange
phoneStr = cell.Value
If regEx.Test(phoneStr) Then
areaCode = regEx.Execute(phoneStr)(0).SubMatches(0)
cell.Offset(0, 1).Value = areaCode
Else
cell.Offset(0, 1).Value = ""
End If
Next cell
Set regEx = Nothing
End Sub2. To run the macro, click the 
button in the VBA editor. A dialog box will prompt you to select the range containing your phone numbers. The macro will automatically extract the area codes and place them in the column immediately to the right of your selected data (for example, if phone numbers are in column B, area codes will appear in column C).
Tip: This code works for phone numbers that include or omit parentheses, dashes, spaces, or dots immediately after the area code. If you have even greater format complexity, you may further adjust the regular expression in the code. Always review the output and correct any cases where extraction fails, such as numbers with missing area codes or unusual patterns.
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in