Microsoft Office Forums Put numbers within a cell into numerical order.

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: 41
14spar15 is on a distinguished road
Default Put numbers within a cell into numerical order.

Hello, Im 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: 41
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: 712
NoSparks will become famous soon enoughNoSparks will become famous soon enough
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 offline Put numbers within a cell into numerical order. Windows 7 64bit Put numbers within a cell into numerical order. Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,387
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

To 14spar
Add code tags please !!!!!!!!
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
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
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


All times are GMT -7. The time now is 10:12 AM.


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