Edit columns in Notepad++ with TextFX plugin

This post is part 3 of 5 in the series Efficient work using Notepad++

Edit columns in Notepad++ with TextFX pluginNotepad++ with the TextFX plugin makes it quick and easy to do simple column editing. An application like Excel has many advanced features and might be more suitable in some cases. However, I always have Notepad++ open and it is often faster and easier to us the following approach for me.

At work we use JIRA for task management. I often have to create new or edit existing tables in task descriptions. Tables are created by using vertical line | characters. In this example I have a table where I need to change the order of the columns and sort the data. I also use this method when working with CSV files. This is how I do it in a few, simple steps.

Edit columns in Notepad++ with TextFX plugin

Edit Columns in Notepad++
1. Select the character that separates “columns” and copy it to the clipboard (CTRL+C), in this example the vertical line | character is copied to the clipboard.

Edit Columns in Notepad++
2. Select all the text (CTRL+A) and click TextFXTextFX EditLine up multiple lines by (Clipboard Character).

Edit Columns in Notepad++
3. The text is now lined up by the character you selected, in this example the vertical line | character. The text now actually looks like a table with columns.

Edit Columns in Notepad++
4. Hold ALT while you use the mouse to select the “columns” to be moved. You can also hold ALT+SHIFT and use the arrow keys to select instead of using the mouse. Cut the text (CTRL+X).

Edit Columns in Notepad++
5. Hold ALT while you use the mouse to select the “column” where the text should be inserted. You can also hold ALT+SHIFT and use the arrow keys to select instead of using the mouse. In this example you won’t actually select any text, just the empty column at the start of each line where the text should be inserted. Paste the text (CTRL+V).

Edit Columns in Notepad++
6. Select all the text (CTRL+A) and click TextFXTextFX ToolsSort lines case insensitive (at column).

Edit Columns in Notepad++
7. The text is now sorted alphabetically, in this example by the first two “columns”.

Edit Columns in Notepad++
8. Click SearchReplace (CTRL+H), choose Regular expression in the Search Mode and replace all “ +\|” (space, plus sign, backslash, vertical line – do not include quotation marks) with “|” (vertical line – do not include quotation marks). This replaces all instances where there are multiple spaces followed by a vertical line with just a vertical line.

Edit Columns in Notepad++
9. The end result has the same format as the original text, but columns have been moved and data has been sorted.

Who is Cathrine Wilhelmsen?

Cathrine is a SQL Server Data Warehouse architect and Business Intelligence developer. She organizes, volunteers and speaks at SQLSaturdays, and is a proud Microsoft Data Platform MVP and BimlHero Certified Expert. She loves sci-fi, fantasy, coffee, chocolate and smilies :)

26 thoughts on “Edit columns in Notepad++ with TextFX plugin”

Great!
Very useful for a quick reorder and to view a csv in column mode.

Tranks

Thank you, nice trick.

This made me fall in love with notepad++

I have the same need, more or less. But the text I have to work with is already tabbed, and the columns may vary in length (e.g. the text in line 1, field A is longer than the one in line 2, field A: that results in line 1, field B to be “one tab further” than line 2, field B – so that it’s impossible to exactly select “the columns to move”. Any suggestion on how to do that?

(I know my explanation is terrible, I can send you the text I’m working with if needed)

I think I understand what you mean, and I will attempt to explain how I solve that kind of problem. (If my explanation is not good enough or I misunderstood you, please feel free to e-mail me your example.)

If the character that separates your “columns” is a tab, you won’t be able to line up multiple lines by using the tab character. There are a couple of different ways you can get around this, you will just have to experiment to find the solution that works best for you. Basically you will have to manipulate the separator, then do the editing, then manipulate the separator again:

1. Replace all tabs with a tab and a second character, for example a vertical line.
2. Line up multiple lines by the second character. You will now have equally sized “columns” that you can move.
3. Replace all tabs plus the second character with a single tab, or whatever else you need to use as the separator

1. Replace all tabs with a whole bunch of tabs so you’re sure that there is enough whitespace between each “column”
2. The columns are not aligned, but you will be able to select one column at the time
3. Replace all tabs with a single tab, or whatever else you need to use as the separator

It worked like a charm, thank you so much!

Hi Catherine, first of all, I am new to Notepad++. Like in Seans comment, my data is already separated by tabs. I tried both of the solutions you described afterwards, but they did not work out. I tried different plugins, and they are not helping either :(

thank you so much, it worked perfectly :)

Thank you for that.
There is an issue with a single line, though.

A single line CSV cannot be ordered that way. It’s not a huge issue, but I’m still waiting for a “no exception” solution to this.

VERY helpful. Thanks!

Great tutorial, but I have a csv with more than 100.000 rows… there is a way to use ALT+SHIFT and go to the last row quickly? (I tried ALT+SHIFT+CTRL+END but it doesnt work :(

You can’t do it with keyboard commands (that I know of), but you can do it a different way. Put your cursor on the first line in your file, in the column you want the column selection to start, say column 12. Then use your mouse to drag the scrollbar to the bottom. Scroll right or left until the other side of your column select is in view. Now, Alt-Shift-left-click in that column and it will select from the first row to the row you clicked in.

Thank you very much Scott. It worked (with Alt-Shift-leftclick) !!!

You’re very welcome. Notepad++ is amazing, but does have its idiosyncrasies.

I disagree with your opening statement. I use column selections constantly while programming, and Excel couldn’t do a thing with a long array initialization block, where I find I need to add a new column, for example, to all of the rows of the initialization. But with column select (in Slickedit, usually since it has much more advanced column features, but I also use notepad++) it’s a piece of cake to do this.

Masterpiece!

Great post, thanks! Ever run into a memory error? I tried this on a file with about 50k records, but “not enough memory.” Thought I’d see if you ever ran into the same. Thanks again!

Hi Catherine, Thanks much for this very practical site. I have a question that’s been plaguing me for some time concerning multiple column editing…How do you move data up or down in a column without moving data in an adjacent column. I tried everything. I know how to move a line or multiple lines of text with Shift+CTRL, but I only want to move data in a particular column without affecting adjacent columns. Thanks in advance. ~ Sean.

Hi Sean, I’m not sure this is even possible, at least I haven’t found a way to do it in Notepad++ yet. Maybe some of my other readers know how to achieve this? (This is the kind of task I use Excel for, since Excel is built for working with rows, columns and cells.)

Hi, Catherine and Sean. A bit late, but Notepad++ has a “column mode” editing, see here:

https://notepad-plus-plus.org/features/column-mode-editing.html

It’s far from perfect, but maybe you can can use that to achieve what you want.

if there is a ‘ (apostrophe) somewhere in the line, the line up doesn’t seem to work. Is it a known bug ?

I don’t know if it’s a known bug, you’d have to ask the Notepad++ author about that, but I’ve also had problems with apostrophes and slashes. Sometimes it works if you escape the characters (like adding another slash), other times I have simply replaced the characters with something else and then replaced it with the original value afterwards. It’s not the most perfect solution, but it’s usually quicker than other methods :)

Hi Cathrine,

I found out the “Line up multiple lines by (Clipboard Character)’, reformatting my csv file into readable columns. It works fine wit one file at a time, but I have to reformat a few hundred files. Can I do this reformatting with TextFX in one action over all open files?

@P, here is a method for aligning columns in a file that uses Regex replace in any editor. It does require a little footwork since you have to specify the alignment columns (where textfx dynamically calculates the best alignment column) but it can be used in many files, and you can save it off to use later.

• First, replace all commas with 50 spaces (however many you need to guarantee the commas are in free columns) followed by a comma. This is only to give you space to work.
• Now, we must choose the column we want for the first comma, say column 50, then the column for the second comma, and continue until we have defined a match for each comma. So, we create a regular expression to do this, like:
o ^([^,]{50,50})( *,)([^,]{15,15})( *,)(.+)$
o This grabs the first 50 non-comma characters and creates a group out of them,
o the second group is the extra spaces we need to remove in each line followed by a comma,
o the third group is the next 15 non-comma characters,
o the fourth group is again the extra spaces we need to remove,
o the fifth group is everything else.
• The replace string for this regex is:
o \1,\3,\5
o This pastes in the first, third, and fifth columns with commas inbetween (effectively deleting the blocks of spaces you don’t want, resulting in aligned characters).
• A good way to test this is to use one of the regex web sites, paste in part of one file.
• Now all you have to do is tell notepad++ to run this replacement in all opened documents.

To clarify, I should have said to replace all commas with 50 spaces plus the original comma, so you would run a replace in all files for “,” to ” ,”. I don’t know if that second string will display properly, but it’s 50 space characters followed by a comma.

One more clarification, if you want the commas in each group to be before the spaces rather than after do a search and replace of “( +),” with “,\1”. This matches all spaces before a comma, and pastes in as a comma followed by the exact spaces that were there before, so your columns remain lined up, but the commas are moved.

Also, if it wasn’t clear, you would duplicate the groupings for all available columns and do the replace of all of them at once. This is assuming that all of your files share the same number of columns and can be processed with the same column widths. You could encapsulate this whole thing in a macro as well, if you are going to edit many files over time with this same structure (Catherine has blog entries to help with that).

Share Your Thoughts?