Can I sort the text after a certain character?

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?

Syztemlord

Posted 2015-07-23T12:17:37.487

Reputation: 73

Thanks for the quick answers, managed to get it sorted through Notepad++ – Syztemlord – 2015-07-23T15:18:05.777

Answers

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++

DavidPostill

Posted 2015-07-23T12:17:37.487

Reputation: 118 938

1This 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 – 2015-07-23T13:01:09.773

I use a portable version of Notepad++ on a USB stick for exactly that reason ;) – DavidPostill – 2015-07-23T13:02:23.347

Now if only we were allowed to plug in USB sticks! (Bank policy, they are turned off. But we can use Excel!) :-( – Denham Coote – 2015-07-23T13:03:15.250

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 – 2015-07-23T13:15:18.330

@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 – 2015-07-23T13:42:01.057

@GroundZero Not without going through infosec. Not if I want to stay un-fired... – Denham Coote – 2015-07-23T14:08:41.097

@DavidPostill Well, maybe they left it out of the copy I downloaded. I like, and prefer, your solution, but since the OP asked for Excel, it's a fair enough answer. – Denham Coote – 2015-07-23T14:10:27.560

@DenhamCoote According to TextFX menu is missing in Notepad++, "It was removed from the default installation as it caused issues with certain configurations, and there's no maintainer."

– DavidPostill – 2015-07-23T15:29:32.947

Good update to your answer, I'm sure the additional install instructions will come in useful – Denham Coote – 2015-07-24T11:43:50.777

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

Denham Coote

Posted 2015-07-23T12:17:37.487

Reputation: 455

This solution requires an extra column of data :/ – DavidPostill – 2015-07-23T12:55:04.640

2As does the other Excel solution. But this works for longer data. OP did ask for solutions in either... – Denham Coote – 2015-07-23T12:59:03.443

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 – 2015-07-23T15:21:58.430

@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 – 2015-07-23T15:24:32.223

1@DavidPostill's NotePad++ solution (which I upvoted, btw) doesn't need a big bloated piece of software to run, either ;-) – Denham Coote – 2015-07-23T15:26:33.983

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

Gary's Student

Posted 2015-07-23T12:17:37.487

Reputation: 15 540

This solution requires an extra column of data :/ – DavidPostill – 2015-07-23T12:55:11.970

@DavidPostill Clearly you are correct! – Gary's Student – 2015-07-23T12:57:21.337

Unlike my Notepad++ solution ;) – DavidPostill – 2015-07-23T12:58:10.593

@DavidPostill I like your solution.....I am glad you posted it...Notepad++ looks really neat! Is it free?? – Gary's Student – 2015-07-23T13:00:49.417

@Gary'sStudent Although Notepad++ was mentioned in a different answer, I'll still reply to you: You can get Notepad++ for free at https://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 – 2015-07-23T13:40:00.103

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

Sumyrda - Reinstate Monica

Posted 2015-07-23T12:17:37.487

Reputation: 121

Asked: 2015-07-23T12:17:37.487

Viewed: 3 525 times

Active: 2015-08-18T06:57:48.033