hi, I have these array values in a cell, (16;23;48;5;8). I would like to return them in order in another cell like this, (5;8;16;23;48). is this possible? thank you
hi, I have these array values in a cell, (16;23;48;5;8). I would like to return them in order in another cell like this, (5;8;16;23;48). is this possible? thank you
Are you still using Excel 2010?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
They are all in one cell ?
That's what the OP said in the opening post.
I'm optimistic that most anything is possible with time, effort and ingenuity. Here's how I would probably approach this:
1) something like what you show (a;b;c;d) is not any kind of array in a spreadsheet, it is a text string. The first thing I would do would be to split that so that each value is in its own cell. A text to columns command (with ";" as the delimiter) will quickly and easily divide this text string into separate values.
2) Then a simple sort command will easily sort the values left->right.
3) Then a simple concatenation formula will rejoin the values into a text string like you started with. =A1&";"&B1&";"&...
Of course, some of this becomes more complicated if you are not allowed to use the text to columns or the sort commands. If something like this won't work because you must use formulas rather than these commands to perform the task, then let us know. You might also check your profile, because it says you are still using Excel 2010, and Excel version could be important so we know what formulas are available to you (or if you are even required to use Excel at all for this -- I think this would be easier in google sheets if you are required to use formulas but not necessarily required to use Excel).
Originally Posted by shg
One way:
How to install your new codePlease Login or Register to view this content.
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)
[B][I]
Then:
=CellSort(A3)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
If you have upgraded to O365, then this will also work:
=TEXTJOIN(";",TRUE,AGGREGATE(15,6,MID(SUBSTITUTE(";"&A4,";",REPT(" ",100)),100*(SEQUENCE(LEN(A4)-LEN(SUBSTITUTE(A4,";",""))+1)),100)+0,SEQUENCE(LEN(A4)-LEN(SUBSTITUTE(A4,";",""))+1)))
I've got 365. thanks
that worked perfectly thanks Glenn
OK - please update your forum profile.
thanks. I did the transpose function along with data/text to columns and a sort. However, I have thousands of rows and it's a long manual task. thanks anyway
Whom are you addressing?
thanks MrShorty. I did the transpose function along with data/text to columns and a sort. However, I have thousands of rows and it's a long manual task. thanks anyway
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Bookmark and learn #6 Mr Glenn custom function code, provide the formula as follows
Array formula
HTML Code:
If you have the latest version of Excel including the LET function, try
Formula:Please Login or Register to view this content.
If you're willing to use some VBA,
Then use this along with the SORT function in formulas likePlease Login or Register to view this content.
Formula:Please Login or Register to view this content.
Last edited by hrlngrv; 12-29-2020 at 01:18 AM. Reason: supplement
If cell contains "16;23;48;5;8" without parentheses (), then
Formula:Please Login or Register to view this content.
If cell constains "(16;23;48;5;8)" with parentheses (), then
Formula:Please Login or Register to view this content.
With all of the different options being offered, I guess I will offer one, too. Only mine only works in Google Sheets, but I think it is easier to understand what it is doing:
1) A split() function to convert the text string to a horizontal array split(A1,";") [Excel does not support a split function, as far as I know].
2) transpose() to transpose to a vertical array transpose(split(...))
3) sort() to sort the vertical array sort(transpose(...),1,true)
4) textjoin() to convert the resulting vertical array to a semicolon delimited text string. =textjoin(";",false,sort(...))
It probably doesn't change anything, since it only works in Google Sheets, but it muddies the waters with yet another choice for the OP.
Picky and arcane, but XLM to the rescue. If these cells were in column A, say, A3:A1002, make A3 the active cell, then define the name split referring to
Formula:Please Login or Register to view this content.
Called as =split, this returns a vertical array of strings.
That said, should Excel have a worksheet SPLIT function? Yes. Will it any time soon? Ponder how long it's been since the first calls for regular expression functions in Excel in USENET posts in the 1990s, and still none in Excel. Maybe when our great grandchildren are nearing retirement.
I do hope there are no forum rules against MSFT-directed snark.
One advantage to the XLM above.2) transpose() to transpose to a vertical array . . .
So many options. However, VBA user-defined functions are more maintainable than magic defined names calling XLM functions.
Love it!
This is right up there with =SUMPRODUCT(1/COUNTIF(x,x)) to count distinct items in x.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks