Format MAC addresses in cells by adding colon symbol in Excel

Imagine you have a worksheet filled with a list of alphanumeric strings, and your goal is to transform each alphanumeric value in these strings into MAC addresses by inserting colons, as illustrated in the following screenshot. How can you efficiently achieve this? This tutorial provides straightforward methods to help you tackle this challenge.

Format number and text as mac address with formula
Easily format number and text as mac address with Kutools for Excel
Format number and text as mac address with VBA

Format number and text as mac address with formula

1. Select a blank cell, enter the following formula and then press the Enter key to get the first result. See screenshot:

   =LEFT(A2,2)&":"&MID(A2,3,2)&":"&MID(A2,5,2)&":"&MID(A2,7,2)&":"&MID(A2,9,2)&":"&RIGHT(A2,2)

   Copy
   
   Note: In the formula, A2 is the first cell in the list of strings to be formatted as mac format.
2. Select the first result cell, drag its Fill Handle down to get the rest of mac addresses. See screenshot:
   

Easily format number and text as mac address with Kutools for Excel

The formula provided in the method above does not look very easy to grasp. If you are looking for a simpler solution, then the Add Text utility of Kutoos for Excel is highly recommended. Using this utility, you can easily add colons to cells at specified positions to quickly format the string in those cells as MAC addresses. Please follow the steps below.

1. Select the cells that contain the strings that need to be formatted as mac addresses.
2. Enter a colon in the Text box.
3. Select the Specify position option, and then enter the numbers that represent the locations where you want to add the colons.
   Tip: In this case, I need to add a colon after every two numbers in cells, so I enter 2, 4, 6, 8 and 10 separated by commas. See screenshot:
4. Click the OK button.
   

Result

Strings in the selected cells are now formatted as mac address as shown in the screenshot below.

Format number and text as mac address with VBA

You also can apply VBA code to format number and text as mac address in Excel. Please do as follows.

1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the new Module code window.
   
   VBA code: Format numbers as mac addresses in cells

   Sub FormatMAC()
   'Updated by Extendoffice 20231103
       Dim I As Long
       Dim xRg As Range
       Dim xCell As Range
       Dim xVal As String
       Dim xStr As String
       On Error Resume Next
       Set xRg = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
       If xRg Is Nothing Then Exit Sub
       On Error GoTo 0
       For Each xCell In xRg
           xVal = xCell.Value
           If InStr(xVal, ":") > 0 Then
               xVal = Replace(xVal, ":", "")
           End If
           For I = 1 To Int(Len(xVal) / 2)
               xStr = xStr & Mid(xVal, 2 * I - 1, 2) & ":"
           Next
           xCell.Value = Left(xStr, Len(xStr) - 1) 'Remove the last ":"
           xStr = ""
       Next
   End Sub
   

   Copy
3. In the opening Kutools for Excel dialog box, select the cells that contain the strings you want to format as mac address, and then click the OK button.
   

Then you can see the selected cells are formatted as mac address as shown in the screenshot below.

Related articles:

• How to add text in middle of selected cells in Excel?
• How to add comma at end of cell/text in Excel?
• How to add a character before each word in a cell in Excel?

Best Office Productivity Tools

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
	Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
	Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
	Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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...

More details of Kutools for Excel... Free Download...

---

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!

More details of Office Tab... Free Download...

---

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

More details of Kutools for Office... Free Download...