Microsoft Office Forums Put numbers within a cell into numerical order.
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
04-07-2019, 03:59 PM
 14spar15 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Mar 2011 Posts: 41
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
#2
04-07-2019, 08:39 PM
 14spar15 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Mar 2011 Posts: 41
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
#3
04-07-2019, 10:26 PM
 NoSparks Windows 7 64bit Office 2010 64bit Excel Hobbyist Join Date: Nov 2013 Location: British Columbia, Canada Posts: 712

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```
#4
04-07-2019, 11:34 PM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,387

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.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post btserver Excel 5 03-16-2019 07:54 AM seanspotatobusiness Word 1 07-26-2018 03:05 PM TechDiva95 Word 10 02-24-2018 10:06 AM Edward 012345 Word 2 10-19-2016 03:13 PM Ife Excel 0 04-23-2012 10:35 AM

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

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top

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