Create a list of VLAN commands in Excel

First, open a new Excel workbook and import the list of VLANs from the “show interface gigabit vlan” command in VOSS. Prepare the data in Notepad ++ by inserting a “=” as a column break between each word to help with the import into columns. You want a single cell with a list of VLANs.

Identify a free cell in the sheet and use the Data tab Text to Column tool to split the list of comma separated VLANs into new columns.

Select all of the VLAN columns and paste into a new cell using Transpose which will create a list of VLANs in a single column.

Always ensure the columns are set as type “Text”.

Insert a new column alongside the previous one and add the interface number alongside each VLAN.

Then we can use the concatenate formula to combine the VLANs and interface at the end of the string “vlan members add”.

For example, the formula would be =CONCATENATE(“vlan members add “,O2,” “,P2) placed in the adjacent cell in a new column alongside the VLAN and Interface columns. We can then select the cell and drag it down the rest of the column to create the list of VLAN commands needed to add a long list of VLANs to an individual port.

Alternatively, use GREP to extract all of the “vlan members” lines from the saved config file and import into Excel (into columns). Add a column alongside “members” which can contain “remove” or “add”. If needed remove the slot and “/” characters from the port numbers if applying on a switch with a different format. Higher speed ports may need changing manually as well. Create a new column which concatenates the cells to build the commands to assign VLANs to ports. This method probably cleaner.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s