sort special text/numbers in format with many dots

[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

 

[Roger Govier]

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.

 

[Marco]

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.

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

Thx
Marco

 

[Roger Govier]

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

 

[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 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

 

[Roger Govier]

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"))

 

[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"))

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

 

[Roger Govier]

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.

 

[Marco]

Then stay with the comma, rather than semicolon, but go to Control

Panel>Regional Settings and change your separator from semicolon to comma.

Hi Roger

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

Regards
Marco

 

[Roger Govier]

Hi Marco

Then it sounds as though you will need to translate each of the functions
into their German equivalent.

 

[Pete_UK]

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

 

[Marco]

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

 

[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

 

[Pete_UK]

Glad to hear that you solved the problem - thanks for feeding back.

Pete