3 Replies Latest reply on Jan 29, 2012 2:06 AM by Sorbsbuster

    Mixing Text and numbers

    tedmather

      Title

      Mixing Text and numbers

      Post

      How do you mix numbers and text and sort properly? We designate lighting fixtures with codes that might be any of the following: 1, 2, 2A, 2A-1, TA, TA-19, TA-26, BG, BG1, BG2, etc. 

      I cannot figure out a way to sort them the way we want, since a field can only be either a number or text. Is there a way to convert text to a number (ie conver A to 1, B to 2, etc). If so, I could possibly split each code into several fields, and convert each letter to a number "behind the scenes", but I can't find any method of converting text to a number. 

      I would think this is a common requirement with serial numbers that mix text and numbers.

      Help!

      Ted

        • 1. Re: Mixing Text and numbers
          LaRetta_1

          Hi Ted,

          I am not clear on how you would like them to sort.  Try this, create a calculation (result is text) where string is your field:

          Right ( "000000" & Filter( string  ; "0123456789" ) ; 6 )
          &
          Filter ( Upper ( string ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

          And then sort on this calculation.  How does it look?  It says that numbers are first in importance and then the text.  It pads the numbers as well.  I did not know whether to include dashes, periods etc in the sort criteria.  If you wanted to count them then I could have used KanjiNumeral() to only remove the numbers but leave word separators.

          This wouldn't be something to display - only sort by.  This is just to get you going.  You need to clearly define the rules before you can write the calculation.  What does you mind do when it mentally sorts it?  Let us know and we can adjust this for you.  :^)

          You said, " I would think this is a common requirement with serial numbers that mix text and numbers."

          Well, no.  Most times serials are designed as straight numbers so sorting isn't an issue.  If those numbers or text prefix/suffix means something then they should be broken out into a field of their own (one fact per field is the idea in database management).  Once the meaning is translated into their proper fields or style then those fields are used to sort, ie, first by Type (which might be the BG, TA) and then by Version (which may be 2A, 2A-1) ...

          Many times we inherit older systems with numbering codes which are 'retired' but yet we must maintain for backward compatibility.  If this is such the case (the codes have no real meaning) then you are stuck sorting by a specific calculation definition as we are doing. Let us know if you get stuck once you list out the 'human' sort order rules. :^)

          • 2. Re: Mixing Text and numbers
            tedmather

            Thanks! I'll give this a whirl. Regarding serial numbers - I was thinking specifically of software licenses, which are often a mix of both (my 35 digit FileMaker key comes to mind - 14 numbers and 21 letters!).

            Depending on the project, our codes tend to all start with a number, with letters as the modifier (1,2,2A, 2B, 3, etc), or the other way around (TA, TB, TB-1, TB-2, etc). But yes, we could set the rules. Let me try this.

            Ted

            • 3. Re: Mixing Text and numbers
              Sorbsbuster

              "I was thinking specifically of software licenses" - yes but in computing Consistency is next to Godliness - it is always 35 digits long, so simply storing it as text will sort it the way most of us would anticipate: 0 - 9 first, a-z after.  I think your bigger problem will be the human one of deciding how you want the sort to appear.

              You could try just storing the number as text and sorting it, then looking to see how far away from your idea of perfection that actually is.  If nothing else it would be a good starting point for your team to argue about!