--- title: Excel Macros and Exotel Reports slug: business-phone-system/excel-macros-and-exotel-reports docTags: createdAt: 2025-10-10T16:33:01.794Z --- ## Get Leg1 & Leg2 Duration from Exotel Call Reports If you regularly analyze Exotel call reports in Excel, you can automate extraction of Leg1 and Leg2 durations using a VBA macro. Below are prerequisites, a short explanation of the data, step-by-step instructions to import and run the macro, and the macro source code. ## Prerequisites ### Windows - Excel with the **Developer** tab enabled. (Macros and VBA tools are on the Developer tab, which is hidden by default — see “Show the Developer tab” in Microsoft Office help.) ### Mac - Make the **Developer** tab visible: 1. Go to **Excel → Preferences… → Ribbon & Toolbar**. 2. In **Customize the Ribbon**, check **Developer** and click **Save**. ## About Leg1 & Leg2 Duration Call reports downloaded from the Exotel dashboard include a PriceDetails field that contains an array. That array can include Duration values (in seconds) for: - **Leg1** — the From number (outgoing leg) - **Leg2** — the To number (incoming leg) Note: for a completed incoming call, you will typically see only **Leg2** duration. ## Quick Setup: Run the Macro 1. Download LegDuration.bas. 2. In Excel, open the **Developer** tab and click **Visual Basic**. 3. In the VBA editor: **File → Import File...**, and choose LegDuration.bas. 4. Return to your Exotel call report worksheet. 5. Click **Macros**, select LegDuration, and click **Run**. :::BlockQuote **Note:** The macro was created and optimised for Microsoft Office 2016. It is expected to work on newer versions, but Exotel does not provide troubleshooting support for local macro errors — feel free to inspect or modify the source if needed. ::: ## VBA Macro Source (LegDuration.bas) :::BlockQuote Sub LegDuration() ' LegDuration Macro ' Keyboard Shortcut: Ctrl+Shift+L Columns("O\:O").Select Selection.Copy Columns("S\:S").Select ActiveSheet.Paste Application.CutCopyMode = False Columns("S\:S").Select Selection.Replace What:="Divert", Replacement:="Exc", LookAt:=xlPart, \_ SearchOrder:=xlByRows, MatchCase:=False Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, \_ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, \_ Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo \_ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), \_ TrailingMinusNumbers:=True Columns("T\:T").Select Selection.Delete Shift:=xlToLeft Columns("U\:U").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Range("S1").Select Selection.AutoFilter sheetName = Worksheets(1).Name Set sht = Worksheets(sheetName) Set StartCell = Range("R3") LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column ActiveSheet.Range("$A$1:$T$" & CStr(LastRow)).AutoFilter Field:=19, Criteria1:="\<>\*Leg1\*" Range("T1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC\[-1]" Range("R1").Select Selection.End(xlDown).Select Range("T" & CStr(LastRow)).Select Range("T1\:T" & CStr(LastRow)).Select Range("T" & CStr(LastRow)).Activate Selection.FillDown Range("T1").Select Selection.AutoFilter Columns("T\:T").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks \_ :=False, Transpose:=False Range("S1").Select Application.CutCopyMode = False Selection.AutoFilter ActiveSheet.Range("$A$1:$T$" & CStr(LastRow)).AutoFilter Field:=19, Criteria1:="\<>\*Leg1\*" Range("S2").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Selection.End(xlUp).Select Selection.AutoFilter Columns("S\:T").Select Selection.Replace What:="N/A", Replacement:="", LookAt:=xlPart, \_ SearchOrder:=xlByRows, MatchCase:=False Columns("S\:S").Select Selection.Cut Range("U1").Select ActiveSheet.Paste Columns("S\:S").Select Selection.Delete Shift:=xlToLeft Columns("T\:T").Select Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, \_ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, \_ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar \_ :="D", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("U\:U").Select Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, \_ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, \_ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar \_ :="=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("V\:V").Select Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, \_ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, \_ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar \_ :="s", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("T\:T").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Range("T1").Select ActiveCell.FormulaR1C1 = "Leg1" Columns("S\:S").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("U\:U").Select Selection.Cut Range("S1").Select ActiveSheet.Paste Columns("T\:T").Select Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, \_ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, \_ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar \_ :="D", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("U\:U").Select Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, \_ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, \_ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar \_ :="=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("V\:V").Select Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, \_ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, \_ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar \_ :="s", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("T\:U").Select Selection.Delete Shift:=xlToLeft Selection.End(xlUp).Select ActiveCell.FormulaR1C1 = "Leg2" Range("S1").Select End Sub ::: ## Troubleshooting & Notes - The macro assumes your Exotel call export layout has the PriceDetails (or equivalent) column in column **O**. Adjust column references in the macro if your export format differs. - If you encounter errors, verify: - The Developer tab is enabled and macros are allowed. - The spreadsheet has data starting at row 1 and the expected columns. - The Sheet1 name or ID references match your workbook (if integrating with Google Sheets scripts). - This macro performs multiple text-to-columns and replace operations; always test on a copy of your report.