Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
alphabetical order within a cell
Hi
I have lists of postcodes [zip codes] in cells - many codes together in the same cell - , but only the first two characters. For example: CT, TN, BN, RH, etc etc. There are a lot of them. Could someone tell me how to get Excel to order these alphabetically in each cell that contains them please? Note..this is not about a single code in its own cell but multiple codes in a single cell. Many thanks David |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
alphabetical order within a cell
Take the code below and put it into a code module. Choose the cell to sort
contents on (one at a time) and run this code/macro. I've tried to break long lines so that it can just be cut and pasted, but you never can tell about things here until you actually post. Then it's a little late to edit :( Sub SortOneCellContents() 'choose the cell with contents 'to be sorted before calling 'this routine ' 'we will assume that the 'separator for character groups 'is the space character 'and that the first character in the 'cell is not a space ' 'that is to say, this routine 'parses, sorts, and puts back together 'a cell content that might look 'something like 'CT NV TN OR DC CA MN ' Dim RawCellData As String Dim ToBeSorted() As String Dim TheSeparator As String Dim IsSorted As Boolean Dim BubbleLoop As Integer Dim SwapHolder As String If IsEmpty(Selection) Then MsgBox "Empty Cell" Exit Sub ' no work to be done End If ReDim ToBeSorted(1) 'initialize TheSeparator = " " ' change if something besides space RawCellData = Selection.Value 'force space at end of string if one isn't there 'when we start here If Right(RawCellData, 1) < TheSeparator Then RawCellData = RawCellData & TheSeparator End If Do Until InStr(RawCellData, TheSeparator) = 0 ToBeSorted(UBound(ToBeSorted)) = _ Left(RawCellData, InStr(RawCellData, TheSeparator) - 1) If Len(RawCellData) = Len(ToBeSorted(UBound(ToBeSorted))) + 1 Then RawCellData = "" ' all done Else ' more work to be done 'remove what we just put into the array RawCellData = _ Right(RawCellData, Len(RawCellData) - _ (Len(ToBeSorted(UBound(ToBeSorted))) + 1)) End If 'make room for another - will end up being empty ReDim Preserve ToBeSorted(UBound(ToBeSorted) + 1) Loop 'now a simple bubble kind of sort to get them in ascending order Do Until IsSorted = True IsSorted = True ' not really, but may be later For BubbleLoop = LBound(ToBeSorted) To UBound(ToBeSorted) - 1 If ToBeSorted(BubbleLoop + 1) < ToBeSorted(BubbleLoop) Then SwapHolder = ToBeSorted(BubbleLoop) ToBeSorted(BubbleLoop) = ToBeSorted(BubbleLoop + 1) ToBeSorted(BubbleLoop + 1) = SwapHolder IsSorted = False ' wasn't this time thru End If Next Loop 'now rebuild the string 'reuse BubbleLoop counter and RawCellData for this loop also RawCellData = "" ' just to make sure it's empty For BubbleLoop = LBound(ToBeSorted) To UBound(ToBeSorted) If ToBeSorted(BubbleLoop) < "" Then RawCellData = RawCellData & ToBeSorted(BubbleLoop) & TheSeparator End If Next Selection.Value = Trim(RawCellData) End Sub "David" wrote: Hi I have lists of postcodes [zip codes] in cells - many codes together in the same cell - , but only the first two characters. For example: CT, TN, BN, RH, etc etc. There are a lot of them. Could someone tell me how to get Excel to order these alphabetically in each cell that contains them please? Note..this is not about a single code in its own cell but multiple codes in a single cell. Many thanks David |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
alphabetical order within a cell
Wow. And here I was thinking that Excel was easy to use. But I've pasted
this code into the VB [takes me back] editor and run it, all in the correct manner purely by fluke, and it worked fantastically. Thank you. David |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
alphabetical order within a cell
You're welcome. Excel is easy to use. Just some things that don't have
built in functions to solve. This actually could have been done on a worksheet using some of the text parsing functions people have built and then using the Data | Sort feature and then concatenating the results back into a string. But that would have been a lot more manual work for you. Having a variable number of state IDs in the cells was also something that I thought about and it would have complicated the worksheet solution. By the way, that is a very generic solution. It would work on any text contained in a single cell, including variable length strings like perhaps a list of names or colors or such. I think that it could be improved by coding up a different sort, say a heap or shell sort, but for short lists, the simple bubble works fine enough. "David" wrote: Wow. And here I was thinking that Excel was easy to use. But I've pasted this code into the VB [takes me back] editor and run it, all in the correct manner purely by fluke, and it worked fantastically. Thank you. David |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
alphabetical order within a cell
Have to say that that didn't make a whole lot of sense. Double dutch?
Anyway, thanks again. Only fault is that it would have been perfect had it ordered by number as well [not just by anything that began with '1'. -- David Kitching Msc. Msc. Managing Director Natural Deco Ltd. The Manor Manor Lane Loxley Warwickshire CV35 9JX UK. Tel: +44 (0) 1789 470040 Mob: +44 (0) 7799 118518 www.naturaldeco.co.uk "JLatham" wrote in message ... You're welcome. Excel is easy to use. Just some things that don't have built in functions to solve. This actually could have been done on a worksheet using some of the text parsing functions people have built and then using the Data | Sort feature and then concatenating the results back into a string. But that would have been a lot more manual work for you. Having a variable number of state IDs in the cells was also something that I thought about and it would have complicated the worksheet solution. By the way, that is a very generic solution. It would work on any text contained in a single cell, including variable length strings like perhaps a list of names or colors or such. I think that it could be improved by coding up a different sort, say a heap or shell sort, but for short lists, the simple bubble works fine enough. "David" wrote: Wow. And here I was thinking that Excel was easy to use. But I've pasted this code into the VB [takes me back] editor and run it, all in the correct manner purely by fluke, and it worked fantastically. Thank you. David |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
alphabetical order within a cell
Ok - simply put, we could have done this with functions in cells, but we
would have had to take a very long trip through the woods to get to where we wanted to be. The code solution was, in my opinion, easiest to implement. But I could have done some things to make it a little better even. By being generic, I mean it works for any group of text in a cell, not just the types of entries you had. It is independent of length of the groups in a cell even. Try typing in "the quick brown fox jumped over the lazy dog" in a cell and running the code against that cell. :-) "David" wrote: Have to say that that didn't make a whole lot of sense. Double dutch? Anyway, thanks again. Only fault is that it would have been perfect had it ordered by number as well [not just by anything that began with '1'. -- David Kitching Msc. Msc. Managing Director Natural Deco Ltd. The Manor Manor Lane Loxley Warwickshire CV35 9JX UK. Tel: +44 (0) 1789 470040 Mob: +44 (0) 7799 118518 www.naturaldeco.co.uk "JLatham" wrote in message ... You're welcome. Excel is easy to use. Just some things that don't have built in functions to solve. This actually could have been done on a worksheet using some of the text parsing functions people have built and then using the Data | Sort feature and then concatenating the results back into a string. But that would have been a lot more manual work for you. Having a variable number of state IDs in the cells was also something that I thought about and it would have complicated the worksheet solution. By the way, that is a very generic solution. It would work on any text contained in a single cell, including variable length strings like perhaps a list of names or colors or such. I think that it could be improved by coding up a different sort, say a heap or shell sort, but for short lists, the simple bubble works fine enough. "David" wrote: Wow. And here I was thinking that Excel was easy to use. But I've pasted this code into the VB [takes me back] editor and run it, all in the correct manner purely by fluke, and it worked fantastically. Thank you. David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions |