7

I have a large list I'm trying to sort but I'd like to sort the list from the value after a certain character. eg:

241&name=A
123&name=C
645&name=B

So I'd like to sort the list alphabetically by "A, B, C" etc. Can I do this in Notepad++ or Excel?

1
  • Thanks for the quick answers, managed to get it sorted through Notepad++ – Syztemlord Jul 23 '15 at 15:18
13

Notepad++ Solution

  1. "Menu" > "TextFX" > "TextFX Tools" and select one of the following:

    • Clear the check mark for option "+Sort ascending" to sort descending order.

    • Enable the check mark for option "+Sort ascending" to sort ascending order.

    enter image description here

  2. Select the text, either lines of text or columns

    • For column mode select - Press and hold Shift+Alt down, select text with the mouse

    enter image description here

  3. "Menu" > "TextFX" > "TextFX Tools" and select one of the following:

    • "Sort lines case sensitive (at column)"

    • "Sort lines case insensitive (at column)"

    enter image description here

  4. Result (before and after):

    enter image description here enter image description here

Notes:

  • TextFX Tools will sort only the lines you select. If you do a column mode select (Hold Shift + Alt keys down and select text via mouse), then it will sort all the lines that are at least partly selected, but will sort them according to the selected part.

  • The option "+Sort outputs only UNIQUE (at column) lines" can be checked independent of "+Sort ascending" option (meaning it will work in either ascending or descending mode). When enabled, duplicate entries will be removed when sorting, leaving only unique entries.

  • When using a rectangular selection, the sort key for a line is the part of that line that is within selection bounds.

Source Sorting lines with plugin TextFX


What if I don't have the TextFX plugin?

Plugin Manager install:

The easiest way to install the plugin is to use the Plugin Manager

  1. "Menu" > "Plugins" > "Plugin Manager" > "Show Plugin Manager"

  2. Select the "Available"

  3. Select "TextFX Characters"

  4. Click " Install"

Manual install:

  1. Close Notepad++

  2. Download the zip file from http://sourceforge.net/projects/npp-plugins/files/TextFX/TextFX%20v0.26/

  3. Extract to the Notepad++ plugins directory.

  4. Run Notepad++

9
  • 1
    This solution requires an additional plugin which isn't present in my default installation of NotePad++. I am behind a corporate firewall and can't install it... :/ – Denham Coote Jul 23 '15 at 13:01
  • I use a portable version of Notepad++ on a USB stick for exactly that reason ;) – DavidPostill Jul 23 '15 at 13:02
  • Now if only we were allowed to plug in USB sticks! (Bank policy, they are turned off. But we can use Excel!) :-( – Denham Coote Jul 23 '15 at 13:03
  • According to Wikipedia (if you can believe it) "The first plugin to be included in the program was "TextFX", which includes features such as W3C validation for HTML and CSS, text sorting, character case alteration and quote handling." – DavidPostill Jul 23 '15 at 13:15
  • @DenhamCoote As long as there is a way to download files to your system (USB, email, command line, ...) you will be able to add the plugin by downloading the necessary file and placing it in the correct folder. By using a portable version of Notepad++, you won't even have to install anything – BlueCacti Jul 23 '15 at 13:42
12

Excel solution

This solution will work for any length text after the =

  1. Paste your data into Excel.
  2. Use the formula =MID(A1,FIND("=",A1)+1,LEN(A1)-FIND("=",A1)) to grab everything after the =
  3. Sort on the new column

enter image description here

5
  • This solution requires an extra column of data :/ – DavidPostill Jul 23 '15 at 12:55
  • 2
    As does the other Excel solution. But this works for longer data. OP did ask for solutions in either... – Denham Coote Jul 23 '15 at 12:59
  • 2
    @DavidPostill Anyone who has seriously worked with flat file databases for any length of time in production and doesn't have the control over the databases or the prioritization to get development resources to make changes, have had to make the compromise of adding key columns and concatenation columns in Excel for the purpose of sorting and slicing data effectively. Denham Coote solution addresses the issue the OP posed. It is quick, can be implemented by an end user, with little overhead if the database is actually larger than the key the OP posted. No need to be negative about it. – AMR Jul 23 '15 at 15:21
  • @AMR I wasn't being negative (I didn't downvote, in fact I upvoted both the answers that weren't mine), it was just an observation. The best solutions to any problem don't add to the data. For example the Notepad++ solution. I appreciate the Excel solution does require extra data ... – DavidPostill Jul 23 '15 at 15:24
  • 1
    @DavidPostill's NotePad++ solution (which I upvoted, btw) doesn't need a big bloated piece of software to run, either ;-) – Denham Coote Jul 23 '15 at 15:26
4

With data in column A, in B1 enter:

=RIGHT(A1,1)

and copy down. Then sort cols A and B by B:

Before:

enter image description here

and after:

enter image description here

5
  • This solution requires an extra column of data :/ – DavidPostill Jul 23 '15 at 12:55
  • @DavidPostill Clearly you are correct! – Gary's Student Jul 23 '15 at 12:57
  • Unlike my Notepad++ solution ;) – DavidPostill Jul 23 '15 at 12:58
  • @DavidPostill I like your solution.....I am glad you posted it...Notepad++ looks really neat! Is it free?? – Gary's Student Jul 23 '15 at 13:00
  • @Gary'sStudent Although Notepad++ was mentioned in a different answer, I'll still reply to you: You can get Notepad++ for free at notepad-plus-plus.org If you had taken the effort to do a simple Google Search, you would've gotten this information immediately instead of having to wait 39minutes... – BlueCacti Jul 23 '15 at 13:40
2

There is a different solution with Notepad++ which doesn't require the extra plugin. I used this input and I assumed that there aren't any # in the input. You could just use a different character for separating your sorting item from your original input.

123&name=Alpha&somethingelse
456&name=Gamma&differentstuff
789&name=Beta&otherstuff
  1. Use a regular expression search & replace to copy the part you want to sort to the front of the line. Make sure to enable regular expressions and use

    Search: (.*)&name=(.*?)&(.*)

    Replace: \2#\1&name=\2&\3

Result:

Alpha#123&name=Alpha&somethingelse
Gamma#456&name=Gamma&differentstuff
Beta#789&name=Beta&otherstuff
  1. Use the sort lines feature.

Result:

Alpha#123&name=Alpha&somethingelse
Beta#789&name=Beta&otherstuff
Gamma#456&name=Gamma&differentstuff
  1. Use another regular expression to undo the changes from step 1.

    Search: (.*)#(.*)

    Replace: \2

Result:

123&name=Alpha&somethingelse
789&name=Beta&otherstuff
456&name=Gamma&differentstuff

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.