Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting, Merging, and Re-merging
A co-worker has a spreadsheet with five columns. One of the columns contains
a string of characters similar to the following: ABC 12345 description of the project BBC 30492 another project description ZZY 01234 a third description of a project She split that column into three columns so that she could sort on the numbers. Now she wants to put the rows back together in the order she sorted them in. Is this possible? Is there a way to sort on those numbers without spliting them into separate columns? Thanks! |
#2
|
|||
|
|||
Is this possible? Is there a way to sort on those numbers without
spliting them into separate columns? The easiest way is to use a helper column of formulas to extract the values on which to base the sort. For example, if ABC 12345 were in cell A1, then the formula: =VALUE(MID(A1,FIND(" ",A1)+1,LEN(A1))) will extract the final number. Copy that down, then select everything and sort on that column. HTH, Bernie MS Excel MVP "Abi" wrote in message ... A co-worker has a spreadsheet with five columns. One of the columns contains a string of characters similar to the following: ABC 12345 description of the project BBC 30492 another project description ZZY 01234 a third description of a project She split that column into three columns so that she could sort on the numbers. Now she wants to put the rows back together in the order she sorted them in. Is this possible? Is there a way to sort on those numbers without spliting them into separate columns? Thanks! |
#3
|
|||
|
|||
Just how did your co-worker actually split the column?
You can split a column using TTC (Text To Columns), where the original data remains *untouched*, then sort on the split column(s), while including the original column within the sort range, and then throw away the split data, leaving the original as is *but* sorted. Select the column, then <Data <Text To Columns, Click "Fixed Width", then <Next, Click on where you wish to create your separations, where you can drag the break lines to wherever, then <Next. In this third window of the TTC wizard, there's a "Destination" box, with the address defaulted to that of the original column. Simply change that to any other column, and the original will remain as is, while the "split" columns will start at the column you entered in this destination box. Then, simply select all the columns, including the original, and sort and set your sort key to whatever column you wish. After sorting, you can throw away the "split" data columns. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Abi" wrote in message ... A co-worker has a spreadsheet with five columns. One of the columns contains a string of characters similar to the following: ABC 12345 description of the project BBC 30492 another project description ZZY 01234 a third description of a project She split that column into three columns so that she could sort on the numbers. Now she wants to put the rows back together in the order she sorted them in. Is this possible? Is there a way to sort on those numbers without spliting them into separate columns? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging Two Cells and Keeping the Values | Excel Discussion (Misc queries) | |||
Merging data from Excel to Word | Excel Worksheet Functions | |||
WRAP a text line over several columns without merging cells | Excel Worksheet Functions | |||
WRAP a text line over several columns without merging cells | Excel Worksheet Functions | |||
sorting question | Excel Discussion (Misc queries) |