Sorting within cells

Discuss the spreadsheet application

Sorting within cells

Postby Dadge » Thu Dec 05, 2019 5:30 am

Hi. I can find lots of info on sorting cells but I haven't found the info I'm looking for re sorting *within* cells.

eg. how to sort cell contents such as

(A1) 3814
(A2) dbja
(A3) t7u1

to

(A1) 1348
(A2) abdj
(A3) 17tu

Thanks!
OpenOffice 4.1.3 on Windows 7 SP1
Dadge
 
Posts: 3
Joined: Thu Dec 05, 2019 5:23 am

Re: sorting within cells

Postby robleyd » Thu Dec 05, 2019 5:59 am

That isn’t a feature of any spreadsheet that I have worked with. To do this would require a macro. Or some fancy footwork with Text to Columns, then transpose rows to columns, sort,rejoin the columns and transpose again. I don’t have Calc at hand to give explicit instructions.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3389
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: sorting within cells

Postby FJCC » Thu Dec 05, 2019 7:13 am

A Python macro to do this is very simple. I do not know Python but with a quick web search I wrote the super bare bones version.
Code: Select all   Expand viewCollapse view
def cell_sort():
  oDoc = XSCRIPTCONTEXT.getDocument()
  oCell = oDoc.CurrentSelection
  oCell.String = ''.join(sorted(oCell.String))
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7678
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: sorting within cells

Postby RusselB » Thu Dec 05, 2019 7:14 am

Quattro Pro used to have this ability, but the latest version that I have, which isn't the latest release, is missing the ability.
You could do it using Text to Columns, then sort Left to Right (sort columns), and then use a concatenation formula to get the sorted data into 1 column.
Based on 4 characters, you're now going to need at least 5 (and I'd recommend going with 6) columns.
Unless you have a lot of data that you need to sort like that, it might be faster to just do it manually.
Additionally, I won't guarantee how the numbers would be handled in the sorting with the text.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6058
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Sorting within cells

Postby Dadge » Sat Dec 07, 2019 3:18 am

Thanks for your kind replies. I'll go away and do some work!
OpenOffice 4.1.3 on Windows 7 SP1
Dadge
 
Posts: 3
Joined: Thu Dec 05, 2019 5:23 am

Re: Sorting within cells

Postby lader » Sat Dec 07, 2019 5:49 pm

Try it
Code: Select all   Expand viewCollapse view
Function SortChars(S$) As String
   Dim L%, J%, K%, X$, Y$: L = Len(S)   
   For J=1 To L-1: X = mid(S, J, 1)
      For K=J+1 To L: Y = mid(S, K, 1)
         If X>Y Then      
            mid(S, J, 1) = Y
            mid(S, K, 1) = X
            X = Y
         EndIf
      Next
   Next
   SortChars = S
End Function
LibreOffice 6.4 on Ubuntu 20.04 LTS
lader
 
Posts: 23
Joined: Mon Jul 02, 2018 6:10 pm

Re: Sorting within cells

Postby Dadge » Sat Dec 07, 2019 7:24 pm

Thanks again. Using find-and-replace to remove the vowels is quite simple, and I can then separate the text into columns, but I'm having trouble with the sorting. For example if I have

P | L | A | T | E
B | L | A | C | K
C | A | N | E
B | A | R | N

a left-to-right sort produces

A | E | L | P | T
A | K | L | B | C
N | | A | C | E
R | | A | B | N

when it should be

A | E | L | P | T
A | B | C | K | L
A | C | E | N
A | B | N | R
OpenOffice 4.1.3 on Windows 7 SP1
Dadge
 
Posts: 3
Joined: Thu Dec 05, 2019 5:23 am

Re: Sorting within cells

Postby FJCC » Sat Dec 07, 2019 8:24 pm

The built in sort function keeps rows or columns in a rectangle together using one or more columns or rows as the key for the sort. I can see in your example that you used the first row as the key and the other rows just follow along. The function is not intended to sort an individual column or row that is embedded in a range of data.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7678
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 10 guests