Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2019, 03:59 PM
14spar15 14spar15 is offline Put numbers within a cell into numerical order. Windows 7 64bit Put numbers within a cell into numerical order. Office 2010 64bit
Advanced Beginner
Put numbers within a cell into numerical order.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Put numbers within a cell into numerical order.


Hello, I’m looking to keep this data in its own cell but in numerical order. In other words, If I have “1999 2001 2004 2003 2000 2002” in A2 I need to have this changed to “1999 2000 2001 2002 2003 2004” in B2 or whatever depending on how many steps it takes. I was thinking I could put each year into its own column, sort the column and then put it back together but I ran into problems even trying to split the string up. There can be anywhere from 1-20 different years if that matters. Thanks
Reply With Quote
  #2  
Old 04-07-2019, 08:39 PM
14spar15 14spar15 is offline Put numbers within a cell into numerical order. Windows 7 64bit Put numbers within a cell into numerical order. Office 2010 64bit
Advanced Beginner
Put numbers within a cell into numerical order.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Is this the right direction?

I found this but it puts each individual digit in order and without spaces. Could it maybe be modified? The string will always consist of 4 digit numbers (Years). Even if each group of four digits could be grouped together and sorted without spaces I could maybe add spaces after sorting. Thanks

Private Function SortCharacters(theString As String) As String
Dim currentChar As String
Dim sourceNum As Integer
Dim destNum As Integer
For sourceNum = 1 To Len(theString)
currentChar = Mid(theString, sourceNum, 1)
If sourceNum = 1 Then
SortCharacters = currentChar
Else
destNum = 1
While destNum <= Len(SortCharacters) And currentChar > Mid(SortCharacters, destNum, 1)
destNum = destNum + 1
Wend
SortCharacters = Left(SortCharacters, destNum - 1) & currentChar & Mid(SortCharacters, destNum)
End If
Next sourceNum
End Function
Private Function SortCharacters(theString As String) As String
Dim currentChar As String
Dim sourceNum As Integer
Dim destNum As Integer
For sourceNum = 1 To Len(theString)
currentChar = Mid(theString, sourceNum, 1)
If sourceNum = 1 Then
SortCharacters = currentChar
Else
destNum = 1
While destNum <= Len(SortCharacters) And currentChar > Mid(SortCharacters, destNum, 1)
destNum = destNum + 1
Wend
SortCharacters = Left(SortCharacters, destNum - 1) & currentChar & Mid(SortCharacters, destNum)
End If
Next sourceNum
End Function
Reply With Quote
  #3  
Old 04-07-2019, 10:26 PM
NoSparks NoSparks is offline Put numbers within a cell into numerical order. Windows 7 64bit Put numbers within a cell into numerical order. Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Try this
Code:
Function SortYears(src As String) As String
  Dim arr As Variant, X As Long, coll As Object
  arr = Split(src, " ")
  Set coll = CreateObject("System.Collections.ArrayList")
  For X = LBound(arr) To UBound(arr)
      coll.Add CStr(arr(X))
  Next X
  coll.Sort
  For X = 0 To coll.Count - 1
      SortYears = Trim(SortYears & " " & coll(X))
  Next X
End Function
Reply With Quote
  #4  
Old 04-07-2019, 11:34 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Put numbers within a cell into numerical order. Windows 7 64bit Put numbers within a cell into numerical order. Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

To 14spar
Add code tags please !!!!!!!!
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort numbers in Ascending order btserver Excel 5 03-16-2019 07:54 AM
Put numbers within a cell into numerical order. Why isn't cross-references dialogue box in numerical order? seanspotatobusiness Word 1 07-26-2018 03:05 PM
How do I Update Caption Numbers appear in Sequential Order TechDiva95 Word 10 02-24-2018 10:06 AM
Put numbers within a cell into numerical order. Placing order numbers on worksheets Edward 012345 Word 2 10-19-2016 03:13 PM
Plotting Numerical and Non-numerical Data Set Ife Excel 0 04-23-2012 10:35 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:29 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft