sort special text/numbers in format with many dots

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


0
no (445)
9/17/2008 2:02:56 PM
excel 39879 articles. 2 followers. Follow

13 Replies
714 Views

Similar Articles

[PageSpeed] 37

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.
-- 
Regards
Roger Govier

"Marco" <no@spam.com> wrote in message 
news:udMLY4MGJHA.1272@TK2MSFTNGP02.phx.gbl...
> 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
>
> 
0
Roger
9/17/2008 2:43:30 PM
> 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


0
no (445)
9/17/2008 3:17:27 PM
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

-- 
Regards
Roger Govier

"Marco" <no@spam.com> wrote in message 
news:e84hBiNGJHA.1268@TK2MSFTNGP05.phx.gbl...
>> 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
>
> 
0
Roger
9/17/2008 8:28:11 PM
> 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 


0
no (445)
9/18/2008 6:36:48 AM
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"))

-- 
Regards
Roger Govier

"Marco" <no@spam.com> wrote in message 
news:uN2CvjVGJHA.2276@TK2MSFTNGP05.phx.gbl...
>> 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
> 
0
Roger
9/18/2008 7:21:45 AM
> 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 


0
no (445)
9/18/2008 7:51:08 AM
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.


-- 
Regards
Roger Govier

"Marco" <no@spam.com> wrote in message 
news:#gyXRNWGJHA.3392@TK2MSFTNGP05.phx.gbl...
>> 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
> 
0
Roger
9/18/2008 8:06:04 AM
> 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 


0
no (445)
9/18/2008 8:22:17 AM
Hi Marco

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

-- 
Regards
Roger Govier

"Marco" <no@spam.com> wrote in message 
news:ORenreWGJHA.536@TK2MSFTNGP02.phx.gbl...
>> 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
> 
0
Roger
9/18/2008 8:31:05 AM
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=C4NGE
TEXT                     TEXT

Hope this helps.

Pete


On Sep 18, 9:22=A0am, "Marco" <n...@spam.com> wrote:
> > Then stay with the comma, rather than semicolon, but go to Control
> > Panel>Regional Settings and change your separator from semicolon to com=
ma.
>
> Hi Roger
>
> The same result, I've got #NAME? in each column
>
> Regards
> Marco

0
pashurst (2576)
9/18/2008 8:49:21 AM
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 


0
no (445)
9/18/2008 9:05:07 AM
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 


0
no (445)
9/18/2008 12:47:36 PM
Glad to hear that you solved the problem - thanks for feeding back.

Pete

On Sep 18, 1:47=A0pm, "Marco" <n...@spam.com> wrote:
> Ok, problem solved with the help from another good guy :-)
> With this public function:
>
> Public Function Sort_Index(rngZelle As Range, strTrenner As String, Optio=
nal
> intLen As Integer =3D 3) As String
> Dim A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 As Variant
> Dim intI =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0As Integer
> Dim strLen =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0As String
>
> =A0 =A0 For intI =3D 1 To intLen
> =A0 =A0 =A0 =A0 strLen =3D strLen & "0"
> =A0 =A0 Next intI
>
> =A0 =A0 A =3D VBA.Split(rngZelle.Text, strTrenner)
> =A0 =A0 For intI =3D 0 To UBound(A)
> =A0 =A0 =A0 =A0 Sort_Index =3D Sort_Index & Format(A(intI), strLen)
> =A0 =A0 Next intI
> End Function
>
> For call the function in coulumn B:
>
> =3DSort_Index(A1;".";5)
>
> So, thank you all for your help!
>
> Kind regards
> Marco

0
pashurst (2576)
9/18/2008 12:57:28 PM
Reply:

Similar Artilces: