Hi There,
Need help in finding a formula to arrange text ascending and descending from each cell.
Eg: Cell A1 has "EDDIE" , results in cell B1 (ascending) should be"DDEEI" and results in cell C1 (descending ) should be "IEEDD"
Thanks
Hi There,
Need help in finding a formula to arrange text ascending and descending from each cell.
Eg: Cell A1 has "EDDIE" , results in cell B1 (ascending) should be"DDEEI" and results in cell C1 (descending ) should be "IEEDD"
Thanks
Hi eddiedhs. Welcome to the forum.
I would be surprised if this can be done without VBA.
Try this user defined function by tigeravatar. If you are not familiar with how to install VBA code see the instructions following this code.
How to install your new code'tigeravatar ExcelForum Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String 'Created by TigerAvatar at www.excelforum.com, September 2012 'Purpose is to concatenate many strings into a single string 'Can be used with arrays, range objects, and collections Dim DataIndex As Variant 'Used to loop through arrays, range objects, and collections Dim strResult As String 'Used to build the result string 'Test if varData is an Array, Range, or Collection If IsArray(varData) _ Or TypeOf varData Is Range _ Or TypeOf varData Is Collection Then 'Found to be an, array, range object, or collection 'Loop through each item in varData For Each DataIndex In varData 'Check if the item isn't empty If Len(DataIndex) > 0 Then 'Found the item isn't empty, check if user specified bUnique as True If bUnique = True Then 'bUnique is true, check if the item has been included in the result yet If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then 'Item has not been included in the result, add item to the result strResult = strResult & "||" & DataIndex End If Else 'bUnique is not true, add item to the result strResult = strResult & "||" & DataIndex End If End If Next DataIndex 'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter strResult = Replace(Mid(strResult, 3), "||", sDelimiter) Else 'Found not to be an array, range object, or collection 'Simply set the result = varData strResult = varData End If 'Output result ConcatAll = strResult End Function
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
Then try array entering this formula in B1. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Change SMALL to LARGE to reverse the order.Formula:=concatall(CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),"")
Dave
Hi FlameRetired ,
Thanks for this, but i cannot get it work
Edd.xlsm
thanks a ton, it works now
.
Pure VBA method :
Website Resource:Option Explicit Public Sub SortContents() Dim oCell As Range Dim I As Integer, J As Integer, iLen As Integer Dim strChr() As String, strWk As String For Each oCell In Selection strWk = oCell.Value iLen = Len(strWk) ReDim strChr(1 To iLen) For I = 1 To iLen strChr(I) = Left(strWk, 1) strWk = Right(strWk, Len(strWk) - 1) Next I For I = 1 To iLen - 1 For J = I + 1 To iLen If strChr(J) < strChr(I) Then strWk = strChr(I) strChr(I) = strChr(J) strChr(J) = strWk End If Next J Next I strWk = "" For I = 1 To iLen strWk = strWk & strChr(I) Next I oCell.Value = strWk Next oCell End Sub
https://windowssecrets.com/forums/sh...ell-(excel-xp)
Hi Logit,
Thanks, but i feel the string is broken??
.
No problems here. Works as it should :
Option Explicit Public Sub SortContentsAscend() Dim oCell As Range Dim I As Integer, J As Integer, iLen As Integer Dim strChr() As String, strWk As String For Each oCell In Selection strWk = oCell.Value iLen = Len(strWk) ReDim strChr(1 To iLen) For I = 1 To iLen strChr(I) = Left(strWk, 1) strWk = Right(strWk, Len(strWk) - 1) Next I For I = 1 To iLen - 1 For J = I + 1 To iLen If strChr(J) < strChr(I) Then strWk = strChr(I) strChr(I) = strChr(J) strChr(J) = strWk End If Next J Next I strWk = "" For I = 1 To iLen strWk = strWk & strChr(I) Next I oCell.Value = strWk Next oCell End Sub Public Sub SortContentsDescend() Dim oCell As Range Dim I As Integer, J As Integer, iLen As Integer Dim strChr() As String, strWk As String For Each oCell In Selection strWk = oCell.Value iLen = Len(strWk) ReDim strChr(1 To iLen) For I = 1 To iLen strChr(I) = Right(strWk, 1) strWk = Left(strWk, Len(strWk) - 1) Next I For I = 1 To iLen - 1 For J = I + 1 To iLen If strChr(J) > strChr(I) Then strWk = strChr(I) strChr(I) = strChr(J) strChr(J) = strWk End If Next J Next I strWk = "" For I = 1 To iLen strWk = strWk & strChr(I) Next I oCell.Value = strWk Next oCell End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks