Adding points delaminated numbers in cells

**JamesDriver** · 10-25-2012, 06:03 AM

With numbers like these - i.e. with each line of data fitting in 1 cell (6 cells in column A):

2.12.3 (2)
12.12.0 (2)
17.4.0 (4)
9.4.9 (3)
4.4.17 (4)
0.0.1 (1)

How can I add the points delimited numbers in the cells, and sort the cells by – firstly the number within the brackets, and secondly the sum of the points delimited numbers?

The result should look like this:

0.0.1 (1)
2.12.3 (2)
12.12.0 (2)
9.4.9 (3)
17.4.0 (4)
4.4.17 (4)

Any help would be greatly appreciated
Regards

**TMS** · 10-25-2012, 06:12 AM

You could, maybe, use Text to Columns using point/dot/full stop/decimal point and space as delimiters. Then sort on the last column and add up the others.

Regards, TMS

**JamesDriver** · 10-25-2012, 06:50 AM

Hmmm.... Sorry, but I don't understand your answer

. I cannot change the way the data looks, and it is already Delimited.
Regards

**TMS** · 10-25-2012, 07:26 AM

HTML Code:

Sort on column F; total in column G ... sort on that too, if you wish.

Regards, TMS

**JamesDriver** · 10-25-2012, 11:50 AM

Yes that works well, many thanks.

Now that only leaves me with one last problem, I have multiple columns sided by side with this type of data and each column needs to be sorted column for column and without distorting the data in the cells. Unfortunately I am a bit rusty on Excel, but the way I see it – if I make the formulas work in the background on another worksheet, how do I then link the sorting on that background worksheet so as to have the data sorted on the main worksheet page? Maybe this is a silly question but I don’t know the answer

**TMS** · 10-25-2012, 12:23 PM

I've never tried to do text to columns to another sheet. Do you not have enough free columns to the right of the data?

Failing that, do each one separately and record it in a macro. Save the values of the sort field and the total and repeat the process as required.

The other option is to write a UDF to extract the total and the sort field.

Regards, TMS