|
tomos
|
 |
« on: January 08, 2010, 02:42:26 PM » |
|
I have a list of names - they are actually file names but they arent filesThe format of the names is below - where X or Z are letters or numbers or underscores or '-' and spaces are spaces. XXXXXXX XXXXXXXX ZZZZZZZZZor XXXXXXX ZZZZZZZZI want to be able to take the last part of the name (ZZZZZZ) and put it in front so I would end up with ZZZZZZZZZ XXXXXXX XXXXXXXX or ZZZZZZZZ XXXXXXXBasically I want to be able to sort the list via the ZZZZZZZZ part of the name Maybe this should be in new requests (?) or maybe there is something out there that does this already ? I dont have Excel but do have Softmaker Office - dont know if it's possible via that... TIA  Tom |
|
|
|
|
Logged
|
|
|
|
|
AndyM
|
|
« Reply #1 on: January 08, 2010, 03:38:39 PM » |
|
I dont have Excel but do have Softmaker Office - dont know if it's possible via that...
probably is possible is the ZZZZ... always the same number of digits and always at the end? if not, does the ZZZZ... always begin just after the rightmost space? if either of these is so, in a column adjacent to your list have a formula that extracts the ZZZZ...(see string formula help), or enough of it to produce a unique list, and then sort the list based on that column. If you have to have the names actually renamed (just sorting isn't sufficient), you can continue the exercise to construct a string ordered the way you want, and then copy just values from that column (not formulas) to another column to get the list of names. Alternatively if you have Word, you could do a Search and Replace to move the ZZZZ... to the front of each name. Any program with a sophisticated enough Search & Replace could be used. Or someone here can whip up a quick script (which I will keep for future reference!) |
|
|
|
« Last Edit: January 08, 2010, 03:42:39 PM by AndyM »
|
Logged
|
|
|
|
|
f0dder
|
|
« Reply #2 on: January 08, 2010, 04:19:07 PM » |
|
sounds like a pretty simple regex job that any decent text editor should be able to handle |
|
|
|
|
Logged
|
 - carpe noctem |
|
|
|
|
snail02
|
|
« Reply #3 on: January 08, 2010, 04:37:22 PM » |
|
There is a very easy way to do this.
1. First save all the names on a notepad file.
2. Open a new document in Microsoft Excel (or any other spreadsheet software).
3. Select Import "data", and import the notepad file.
4. You can then configure how the data should be imported into the worksheet. If you choose by space or tab, then it will put the words of each line in separate columns.
5. Simply cut the last column and bring it to the front.
6. Now select the entire file list and press ctrl+c. Paste it into a new notepad document.
7. You might notice large gaps between the words. Copy the large gap. Click ctrl+h. Paste it in, and replace with regular one-letter width space.
Hope that works.
|
|
|
|
|
Logged
|
|
|
|
|
AbteriX
|
|
« Reply #4 on: January 09, 2010, 09:05:21 AM » |
|
The format of the names is below - where X or Z are letters or numbers or underscores or '-' and spaces are spaces.
XXXXXXX XXXXXXXX ZZZZZZZZZ
or
XXXXXXX ZZZZZZZZ
I want to be able to take the last part of the name (ZZZZZZ) and put it in front so I would end up with
ZZZZZZZZZ XXXXXXX XXXXXXXX
or
ZZZZZZZZ XXXXXXX Hy Tom, any editor with search&replace with reg ex mode can do this. FROM: XXXXXXX XXXXXXXX ZZZZZZZZZ XXXXXXX ZZZZZZZZ RegEx: find all greedy till last space, then find the rest RegEx: ^(.+) (.+)$ Explanation: ^ => match start of string () => group what is found for later back referencing . => find one any sign + => find (at least)one or (as many as)more from the term just typed before, one of any sign, in our case. But only till the next term is found. $ => end of string TO: ZZZZZZZZZ XXXXXXX XXXXXXXX ZZZZZZZZ XXXXXXX Replace: all from group two, then an space, then all from group one Replace: \2 \1 or $2 $1, belongs what reg ex engine/style is used So: open Search&Replace in your favorite text editor or Office Find: ^(.+) (.+)$ Repl: $2 $1 [X] RegEx Depending of how your string looks in real, it could be that this RegEx has to be fine tuned. PM me if you like. HTH? CU |
|
|
|
|
Logged
|
Greetings, Stefan.
|
|
|
|
tomos
|
|
« Reply #5 on: January 09, 2010, 09:27:09 AM » |
|
I started reading about regex lately - I didnt get very far so your explanation is very helpful Stefan  Will try it out later or tomorrow (only passing through now) & will try snail02's as well Thanks all |
|
|
|
|
Logged
|
|
|
|
|
IainB
|
|
« Reply #6 on: January 10, 2010, 07:46:08 PM » |
|
Or, you could just copy paste the strings (names) from Word to an Excel spreadsheet, and if Excel does not automatically set them into columns but just one column, then you could maybe save time and treat each cell as a compound name with
" " (space character) as a delimiter.
Then you could use Excel's string-handling functions.
For example, with a string which has a compound name "Firstname Middlename Lastname" something like:
Search compound name for First name: =LEFT(A2,SEARCH(" ",A2,1)-1)
Search compound name for Last name: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
If ZZZ is always the last string in the compound name and you want it to become the first string, then this would be a relatively simple exercise to extract and sort out in Excel, and then it would be automated for the next time you needed to do it.
|
|
|
|
|
Logged
|
|
|
|
|
tomos
|
|
« Reply #7 on: January 11, 2010, 02:08:24 AM » |
|
Thanks Iain,
I'm afraid I dont have Excel though (mentioned above)
I do have Softmaker office (Planmaker), I can have a look there with your suggestions but I presume they work different
Snail2, your suggestion doesnt work due to some names having three 'parts', some only two
Re the RegEx - can someone recommend a Text Editor for this ? Textmaker doesnt seem to be able to use the regex.
I have a notepad replacement (Win32Pad) - doesnt seem to be able to handle it either
|
|
|
|
|
Logged
|
|
|
|
|
f0dder
|
|
« Reply #8 on: January 11, 2010, 03:12:11 AM » |
|
tomos: try Notepad++ - it's built-in regex engine is relatively weak (because it uses the one from the Scintilla editing component, rather than PCRE/whatever), but should be able to handle a simple regex like this.
|
|
|
|
|
Logged
|
- carpe noctem |
|
|
|
skwire
|
|
« Reply #9 on: January 11, 2010, 03:22:25 AM » |
|
Here's some AHK code to do what you want. Simply copy your text to the clipboard and run this script. The modified output will be copied back out to your clipboard. Alternately, this snippet could easily be modified into a hotkey. Formatted for AutoIt with the GeSHI Syntax Highlighter [ copy or print] Text := Clipboard Block := "" Loop, Parse, Text, `n, `r { RegExMatch( A_LoopField, "^(.+) (.+)$", SubPat ) Block .= SubPat2 . " " . SubPat1 . "`n" } Clipboard := Block
|
|
|
|
« Last Edit: January 11, 2010, 03:24:15 AM by skwire »
|
Logged
|
|
|
|
|
tomos
|
|
« Reply #10 on: January 11, 2010, 04:00:54 AM » |
|
Here's some AHK code to do what you want. Simply copy your text to the clipboard and run this script. The modified output will be copied back out to your clipboard. Alternately, this snippet could easily be modified into a hotkey.
saved [edit] I meant I saved the script! [/edit], thanks Skwire  will get back to you/this later |
|
|
|
« Last Edit: January 11, 2010, 07:54:38 AM by tomos »
|
Logged
|
|
|
|
|
|
|
tomos
|
|
« Reply #12 on: January 11, 2010, 01:04:27 PM » |
|
Well, progress indeed  all methods working First with RegEx: I downloaded both Notepad++ & 2 (in portable formats) and tried both It's like magic - zap zap & everything's reversed I swear I was staring at the screen with my mouth open :O Tom Jones Singer becomes Singer Tom Jones + Jones Singer becomes Singer Jones I also wanted to strip out the last part (so I would be left with the singer's name in the above examples) so, in Notepad++ Find: (.+) (.+) Replace: \1 worked a treat I can see why people like regular expressions - thanks for all the help again people ============================================================= Skwire, that script works perfectly too - I was initially getting error messages, but because I did a 'save as' from the copy or paste box  which of course was html... I will use both methods as I'm getting bits of lists sent to me in dribs & drabs & I need to be able to sort (again using the example above) by 'Name' and by 'Occupation' |
|
|
|
|
Logged
|
|
|
|
|
f0dder
|
|
« Reply #13 on: January 11, 2010, 01:54:32 PM » |
|
Regular expressions can do a lot of nice stuff, and get complex text manipulation over and done with in a jiffy - they're pretty darn nice. Just don't try and go do silly things like parse HTML with regex |
|
|
|
|
Logged
|
- carpe noctem |
|
|
|
IainB
|
|
« Reply #14 on: January 12, 2010, 07:02:23 AM » |
|
@tomos: Sorry - I should have said that you don't need to use Excel. The string handling works just fine in Google Docs spreadsheet also and probably would in Open Office spreadsheet (though I have not tried that). If this were a regular chore that I needed to do, then I would automate it and retain the work as separate sheets in a workbook using something like Excel/Google spreadsheet. The other suggestions in this discussion look like more fun, but seem a tad overly complicated for easy repetitive automation of such a basic task. (Heh. I had to stop myself from suggesting you use string handling routines in Fortran.) 
|
|
|
|
|
Logged
|
|
|
|
|
