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 |

9/17/2008 2:43:30 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 |

9/17/2008 8:28:11 PM

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 |

9/18/2008 7:21:45 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 |

9/18/2008 8:06:04 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 |

9/18/2008 8:31:05 AM