Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number to text (2002)

    I need to convert a number to different formats, for example, "10" as a number to "10th" or "tenth" as a string. Likewise, I'd like to be able to convert "10th" to "10" or "tenth". Is there a format string that can be used with the text function to accomplish this? I know I can build a table with some VBA to perform the lookups without any trouble, but I would imagine there's probably a better way. I want to be able to accomplish this in Excel and Access if possible.

    Any advice would be greatly appreciated.

    Thanks...

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Number to text (2002)

    Frankly, a lookup table is probably the best solution if you want to do that for more than a few numbers. If you did it as a case statement or an IF (or IIF statement in a query) it pretty rapidly gets out of hand, and with a simple table lookup the performance will likely be better in either Excel or Access.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number to text (2002)

    Any suggestions on how to quickly build the values for the lookup table programmatically? I really need to able to handle any number up to three, maybe even four digits. Is there anywhere that I can get all the text format strings available for the text function? If I can even build part of the strings needed, that would be a start. I've searched MS Knowledge base and here for any help, but I haven't found anything that's helped so far. I pasted the table structure for the lookup table below.

    Field Name Data Type
    ID Autonumber
    Nmbr Text Street number (ex. 1, 19, 67)
    TxtFl Text Full text (ex. first, nineteenth, sixtyseventh)
    TxtAb Text Abbreviated text (ex. 1st, ,19th, 67th)

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Number to text (2002)

    None that I know of - many of the abreivated fields are consistent however, and there are a limited set of suffixes, so it shouldn't be too hard to create a small VBA program to do Mod10 operations and then use a Case statement to determine if the suffix is st, nd, rd, or th. For the Full Text fields, once you get past 19, there is a pretty consistent pattern until you get to 100. Hope this helps.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number to text (2002)

    The thread starting at <post#=39092>post 39092</post#> contains some examples of converting number to ordinal, e.g. 9 to "ninth". <post#=236807>post 236807</post#> has a variation.

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Number to text (2002)

    I've seen various functions for converting cardinal numbers to ordinal numbers, some simpler (and more efficient) than others. One "simple" method was "borrowed" from John Walkenbach's Excel formula for this doing this:

    Public Function GetOrdinalNumber(ByVal lngNum As Long) As String

    ' Based on formula used by J. Walkenbach XL 2000 Formulas (Chap 5):
    ' =$A2&IF(OR(VALUE(RIGHT($A2,2))={11,12,13}),"th",IF (OR(VALUE(RIGHT($A2))= _
    {1,2,3}),CHOOSE(RIGHT($A2),"st","nd","rd"),"th"))

    ' If using in query, can use expression on right
    GetOrdinalNumber = lngNum & _
    IIf(Right$(lngNum, 2) = 11 Or _
    Right$(lngNum, 2) = 12 Or _
    Right$(lngNum, 2) = 13, "th", _
    IIf(Right$(lngNum, 1) = 1 Or _
    Right$(lngNum, 1) = 2 Or _
    Right$(lngNum, 1) = 3, _
    Choose(Right$(lngNum, 1), "st", "nd", "rd"), "th"))

    End Function

    Example of function to convert an ordinal (which must be expressed as a string) back to a "regular" (cardinal) number:

    Public Function GetCardinalNumber(ByVal strOrdNum As String) As Variant

    Select Case LCase$(Right$(strOrdNum, 2))
    Case "st", "nd", "rd", "th"
    GetCardinalNumber = CLng(Left$(strOrdNum, Len(strOrdNum) - 2))
    Case Else
    If IsNumeric(strOrdNum) Then
    GetCardinalNumber = CLng(strOrdNum)
    Else
    GetCardinalNumber = strOrdNum
    End If
    End Select

    End Function

    Sample output:
    ? GetOrdinalNumber(1)
    1st
    ? GetCardinalNumber("1st")
    1
    ? GetOrdinalNumber(11)
    11th
    ? GetCardinalNumber("11th")
    11
    ? GetOrdinalNumber(122)
    122nd
    ? GetCardinalNumber("122nd")
    122

    I've also seen numerous functions designed to convert cardinal or ordinal numbers to spelled-out equivalent, some more coherent (and efficient) than others. If I can find a "simple" example will post.

    HTH

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Number to text (2002)

    For an example of code that can be used to convert numbers to text, see this MSDN article:

    Ten Tips for Microsoft Access Developers (Access 2002)

    Then click link to this topic: Convert Numbers to Text (Note - the "Tips" article has some other topics that may be useful.)

    The sample code provided looks pretty comprehensive, and should handle most cases. As for converting text back to numbers, recommend use lookup table for this purpose. It should not be hard to create such a table. I normally do this by "autofilling" a column in an Excel worksheet from 1 to whatever maximum number is needed, then importing the range into an Access table. You can then use the custom conversion functions to populate fields for ordinal number, numbers as text, etc.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •