sort special text/numbers in format with many dots

  • Thread starter Thread starter Marco
  • Start date Start date
M

Marco

Hi

I need your help with sorting in Excel!
I have mani Text fields with numbers into it.

As example:

1
5.1
1.2
10.2.1
1.2.3
2
2.1.5
3
3.2.1.1
3.3.1.1.1

And it should sorted like this

1
1.2
1.2.3
2
2.1.5
3
3.2.1.1
3.3.1.1.1
5.1
10.2.1

How can I sort this like numbers? My problem is, that not all Numbers have
the same format as x.x.x.x! And I can't change this Text-Fields to Numbers,
because 10.6.1 looks the like 37052 :-(

Any suggestions?

Thx
Marco
 
Hi

With your data in column A, insert a blank column at B.
In B1 enter
=IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".",A1)-1)
&"."&SUBSTITUTE(MID(A1,FIND(".",A1)+1
,LEN(A1)),".",""),TEXT(A1,"0.0"))
and copy down as far as required.

Mark columns A and B and sort on Column B ascending.
 
With your data in column A, insert a blank column at B.
In B1 enter
=IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".",A1)-1)
&"."&SUBSTITUTE(MID(A1,FIND(".",A1)+1
,LEN(A1)),".",""),TEXT(A1,"0.0"))
and copy down as far as required.
Click to expand...

Hi Roger, thx for the quick reply!!
But I've got a Error with this code.

Thx
Marco
 
Hi Marco
What's the problem?
The formula is all one line really.
I just broke it up so the newsreader wouldn't cause breaks in funny places
 
Hi Marco
What's the problem?
The formula is all one line really.
I just broke it up so the newsreader wouldn't cause breaks in funny places
Click to expand...

Hi Roger

Yes, the formula is all one line. But I receive the normal error "The
formula contains errors". If I then click to OK the cursor marked the first
".",A1 in the formula.

Any Idea? It's Excel 2007 German but I think all language versions
understand the english syntax, or not?

Regards
Marco
 
Hi Marco

Change the separators from , to ; for your German version.

=IF(ISNUMBER(FIND(".";A1));LEFT(A1;FIND(".";A1)-1)
&"."&SUBSTITUTE(MID(A1;FIND(".";A1)+1;
LEN(A1));".";"");TEXT(A1;"0.0"))
 
Change the separators from , to ; for your German version.
=IF(ISNUMBER(FIND(".";A1));LEFT(A1;FIND(".";A1)-1)
&"."&SUBSTITUTE(MID(A1;FIND(".";A1)+1;
LEN(A1));".";"");TEXT(A1;"0.0"))
Click to expand...

Roger, then I've got #NAME? in every cell in column B
 
Hi Marco

Then stay with the comma, rather than semicolon, but go to Control
Panel>Regional Settings and change your separator from semicolon to comma.
 
Then stay with the comma, rather than semicolon, but go to Control
Panel>Regional Settings and change your separator from semicolon to comma.
Click to expand...

Hi Roger

The same result, I've got #NAME? in each column

Regards
Marco
 
Hi Marco

Then it sounds as though you will need to translate each of the functions
into their German equivalent.
 
If you are using a German version of Excel, then you will have to
translate each of Roger's functions into German. Using Norman Harker's
Functions file (from Debra's site), I can suggest the following to
help you:

IF WENN
ISNUMBER ISTZAHL
FIND FINDEN
LEFT LINKS
SUBSTITUTE WECHSELN
MID TEIL
LEN LÄNGE
TEXT TEXT

Hope this helps.

Pete
 
Ok, thank you booth! Now the formula have now errors anymore.
But the sort order is not correct for all.

18.1 - 18.1
18.10 - 18.10
18.11 - 18.11
18.12 - 18.12
18.13 - 18.13
18.14 - 18.14
18.15 - 18.15
18.16 - 18.16
18.2 - 18.2
18.3 - 18.3
18.4 - 18.4
18.5 - 18.5
18.6 - 18.6
18.7 - 18.7
18.8 - 18.8
18.9 - 18.9

But 18.10, 18.11... should come after 18.9

Many thx for your assistance!
Marco
 
Ok, problem solved with the help from another good guy :-)
With this public function:

Public Function Sort_Index(rngZelle As Range, strTrenner As String, Optional
intLen As Integer = 3) As String
Dim A As Variant
Dim intI As Integer
Dim strLen As String

For intI = 1 To intLen
strLen = strLen & "0"
Next intI

A = VBA.Split(rngZelle.Text, strTrenner)
For intI = 0 To UBound(A)
Sort_Index = Sort_Index & Format(A(intI), strLen)
Next intI
End Function


For call the function in coulumn B:

=Sort_Index(A1;".";5)


So, thank you all for your help!

Kind regards
Marco
 
Back
Top