Sorting Excel by IP address

Select the column with the IP addresses in it and then choose Data>Text to Columns.

The first window should show the data is already Delimited. Click Next.

Set the delimiter to Other and put in a full stop (dot) in the box. Remove any other delimiters if selected. Click Next.

Change Destination to cover four new free columns. For example, enter $R:$U to send IP address octets to. Click Finish.

Select the column with the fourth octet in it and sort on it. This will sort all data based on this index.

Once you have broken down the IP address into octets you can sort the data based on 4th octet or 3rd octet which maybe required based on the network mask. Select a cell in the column you wish to sort and right click the mouse and select Sort.

 

Editing Tips with Notepad++

Notepad++ has many useful features for editing data files and writing scripts such as html, perl or python.

Tip 1: Ctrl + Alt = Select column.

Tip 2: Open two files and on second tab click Move to other view to compare the two files side by side.

Tip 3: Click show all characters radio button to see hidden characters (spaces are dots; tabs are arrows; CRLF). To remove blank spaces select Edit>Blank Operations>Trim Leading and Training Space.

Tip 4: Aligning lines to left use Shift + Tab.

Tip 5: Edit>Clipboard History shows recently copied data. Double click any entry to paste back to an open tab file.

Tip 6: Search Internet for selected word by right clicking the word and choose Search Internet.

Tip 7: Load and Save Sessions (.ses files) to maintain open tabs.

Tip 8: Find key word in all files in a directory folder. Highlight key word and select Search>Find in files. Filter *.* and directory path.

Tip 9: Shortcuts

Ctrl + Shift + Delete = delete line

Ctrl + D = duplicate line

Ctrl + tab = list tabs and file locations

Ctrl + A = select all

Ctrl + X = cut

Ctrl + F = find/replace

Ctrl + Q = comment/uncomment

Ctrl = Z = undo

Ctrl + N = new document

Ctrl + S = save document

Tip 10: Insert characters at beginning of several lines. Select Shift + Alt + down arrow. Enter characters. To remove select Shift + Alt + down arrow + right arrow followed by delete button.

Tip 11: Settings>Preferences>Editing Click Enable (Ctrl+Mouse click/selection) for Mulit-Editing. Can then use Ctrl + left mouse button to select multiple words to replace with another word.

Tip 12: Delete lines containing a word. Select Find>Mark and enter word to search for a click on Bookmark line. Then Search>Bookmark>Remove Bookmarked Lines. Or Search>Replace and enter .* followed by the word and another .* and leave Replace with blank. Use Regular expression search mode. To remove empty lines select Edit>Line Operations>Remove Empty Lines.

Tip 13: Insert incrementing numbers at the start of each line. Select Shift + Alt + down arrow. Stop at appropriate last line. Select Edit>Column Editor and use Number to Insert dialogue.

Tip 14: Replace / Insert word at beginning of line using ^ as Find What filter. Replace / Insert word at end of line using $ as Find What filter.

Tip 15: Extended options

Open the find/replace dialog.
At the bottom will be some Search mode options.  Select “Extended (\n \r \t \0 \x…)”
In either the Find what or the Replace with field entries, you can use the following escapes:
\n  new line (LF)
\r   carriage return (CR)
\t   tab character
\0  null character
\xddd   special character with code ddd

Note: See In selection checkbox to replace all in selected region.

Tip 16: Regular Expressions

[!] finds the exclamation character.

.* selects the rest of the line.

(\+.*)(Item) \+ finds the + character. | .* selects the text after the + up until the word “Item” | Item finds the string “Item” | () allow us to access whatever is inside the parentheses. The first set of parentheses may be accessed with \1 and the second set with \2.

\1\r\n\2 will take + and whatever text comes after it, will then add a new line, and place the string “Item” on the new line.

A-Z finds all letters of the alphabet in upper case.

a-z finds all lower case letters.

A-Za-z will find all alphabetic characters.

[^…] is the inverse. So, if we put these three together: [^A-Za-z] finds any character except an alphabetic character.

Notice that only one of the [^A-Za-z] is in parentheses (). This is recalled by \1 in the Replace with field. The characters outside of the parentheses.

Tip 17: Multiple Instances. Add -multiInst at end of the target line under properties of the shortcut icon.

“C:\Program Files (x86)\Notepad++\notepad++.exe” -multiInst

Alt+F6 also works.

Tip 18: Find text in list of files. Search>Find in Files. Can also search the results again to isolate files further.

Tip 19: Remove empty lines. Edit>Line Operations>Remove Empty Lines.

Tip 20: Selecting multiple lines from large file. Press Ctrl and G. Choose top line by selecting Edit>Begin/End Select. Then choose bottom line by repeating process. Lines from top to bottom line selected will be highlighted.

Tip 21: Sorting IP ARP table output. Open text file in Notepad++ containing lines with IP ARP entries and sort them in ascending order.

Edit>Select All

Edit>Line Operations>Sort Lines As Integers Asceding

Tip 22: Remove blank lines.

Search>Replace

Find what: ^\r\n

Replace with:

Replace All

Tip 22: Remove lines beginning with a # symbol

Search>Replace

Find what: ^[#;].*

Replace with:

Replace All