Sorting within cells

[Dadge]

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!

[robleyd]

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.

[FJCC]

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

def cell_sort():
  oDoc = XSCRIPTCONTEXT.getDocument()
  oCell = oDoc.CurrentSelection
  oCell.String = ''.join(sorted(oCell.String))

[RusselB]

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.

[Dadge]

Thanks for your kind replies. I'll go away and do some work!

[lader]

Try it

Code: Select all

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

[Dadge]

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

[FJCC]

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.