Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Convert text into a number

Discussion in 'Information Technology' started by jlambo, Jul 28, 2006.

  1. jlambo

    jlambo
    Expand Collapse
    Guest

    I have downloaded data into a system data into a text file. I have imported
    this data into access using a link table.

    Included in the data from the text file is data in units of measure ie
    1,234.567 kg. I have set the data type as text

    How do I convert 1,234.567 kg to a numeric (ie 1234.567)
     
  2. Loading...

    Similar Threads Forum Date
    Learn Punjabi Convert Punjabi text Language, Arts & Culture Oct 11, 2013
    Sikhconvert Joins Sikh Philosophy Network! New SPN'ers Aug 28, 2016
    Jehovah's Witnesses Are Out And Converting! Blogs Jul 31, 2016
    Islam Ishna Has Converted To Islam Interfaith Dialogues Apr 1, 2016
    A Christian Missionary tried to convert me. Blogs Oct 22, 2015

  3. Andrew Tapp

    Andrew Tapp
    Expand Collapse
    Guest

    Val(Replace("1,234.567 kg",",","")) returns 1234.567

    Hope this helps.

    "jlambo" wrote:

    > I have downloaded data into a system data into a text file. I have imported
    > this data into access using a link table.
    >
    > Included in the data from the text file is data in units of measure ie
    > 1,234.567 kg. I have set the data type as text
    >
    > How do I convert 1,234.567 kg to a numeric (ie 1234.567)
     
  4. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear JL:

    It would work to remove the comma(s) then convert to the numeric type you
    desire.

    Use Instr() to find the position of commas and concatenate the two parts
    before and after the comma. Do this repeatedly till there are no commas
    remaining. A function would be a good place to do this.

    Tom Ellison


    "jlambo" <jlambo@discussions.microsoft.com> wrote in message
    news:0CF72372-F23D-4160-853C-EAF2A8B8D1AB@microsoft.com...
    >I have downloaded data into a system data into a text file. I have
    >imported
    > this data into access using a link table.
    >
    > Included in the data from the text file is data in units of measure ie
    > 1,234.567 kg. I have set the data type as text
    >
    > How do I convert 1,234.567 kg to a numeric (ie 1234.567)
     
  5. jlambo

    jlambo
    Expand Collapse
    Guest

    Thanks for this. I am relatively new to Access, so I probably did not give
    all the details. I am trying to set up a query. This data is held in a table
    in a column called "Unit of Measure"

    What expression would I need to use in the query to convert this text value
    into a numeric value

    "Andrew Tapp" wrote:

    > Val(Replace("1,234.567 kg",",","")) returns 1234.567
    >
    > Hope this helps.
    >
    > "jlambo" wrote:
    >
    > > I have downloaded data into a system data into a text file. I have imported
    > > this data into access using a link table.
    > >
    > > Included in the data from the text file is data in units of measure ie
    > > 1,234.567 kg. I have set the data type as text
    > >
    > > How do I convert 1,234.567 kg to a numeric (ie 1234.567)
     
  6. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    You can use the Val function in conjunction with the Replace function. Here
    is an example from the Immediate Window:

    ?Val (Replace("1,234.567 xx",",",""))
    1234.567

    The Replace function is needed to remove the comma, since the Val function
    recognizes only the period (.) as a valid decimal separator.

    In a SELECT query, you can use the following in the Field expression:
    NumericPart: Val(Replace([TextField],",",""))


    where [TextField] is the name of the field that contains your imported text
    data. Make the appropriate substitution in the name of the field. You can use
    this in the Update To: row of an Update Query:

    Val(Replace([TextField],",",""))

    Make sure that the numeric field that you are updating is defined as a
    Single. If it is the default Long Integer, you will only get the integer
    portion (1234) of the string. If the units of measure (Kg, for instance) are
    different in various records, then you'll likely want to use a different
    update query to update a UnitsOfMeasure field with this information.


    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "jlambo" wrote:

    > I have downloaded data into a system data into a text file. I have imported
    > this data into access using a link table.
    >
    > Included in the data from the text file is data in units of measure ie
    > 1,234.567 kg. I have set the data type as text
    >
    > How do I convert 1,234.567 kg to a numeric (ie 1234.567)
     
  7. jlambo

    jlambo
    Expand Collapse
    Guest

    Thanks for this Tom

    I have tried using this in my version of Access 2000, however I get the
    following error message

    Undefined function 'Replace' in expression

    "Tom Wickerath" wrote:

    > You can use the Val function in conjunction with the Replace function. Here
    > is an example from the Immediate Window:
    >
    > ?Val (Replace("1,234.567 xx",",",""))
    > 1234.567
    >
    > The Replace function is needed to remove the comma, since the Val function
    > recognizes only the period (.) as a valid decimal separator.
    >
    > In a SELECT query, you can use the following in the Field expression:
    > NumericPart: Val(Replace([TextField],",",""))
    >
    >
    > where [TextField] is the name of the field that contains your imported text
    > data. Make the appropriate substitution in the name of the field. You can use
    > this in the Update To: row of an Update Query:
    >
    > Val(Replace([TextField],",",""))
    >
    > Make sure that the numeric field that you are updating is defined as a
    > Single. If it is the default Long Integer, you will only get the integer
    > portion (1234) of the string. If the units of measure (Kg, for instance) are
    > different in various records, then you'll likely want to use a different
    > update query to update a UnitsOfMeasure field with this information.
    >
    >
    > Tom Wickerath, Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    > "jlambo" wrote:
    >
    > > I have downloaded data into a system data into a text file. I have imported
    > > this data into access using a link table.
    > >
    > > Included in the data from the text file is data in units of measure ie
    > > 1,234.567 kg. I have set the data type as text
    > >
    > > How do I convert 1,234.567 kg to a numeric (ie 1234.567)
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    You doing this in a query, or just in VBA?

    If it's in a query, it sounds as though you haven't applied all of the
    service packs: Office 2000 had this problem when it first came out. The
    usual work-around was to develop your own wrapper function for the Replace
    function:

    Function MyReplace(ToSearch As String, _
    FindString As String, _
    ReplaceString As String) As String

    MyReplace = Replace(ToSearch, FindString, ReplaceString)

    End Function

    and then use MyReplace instead of Replace.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "jlambo" <jlambo@discussions.microsoft.com> wrote in message
    news:F33122AB-C701-4F2F-98D0-72AD55C1FF4D@microsoft.com...
    > Thanks for this Tom
    >
    > I have tried using this in my version of Access 2000, however I get the
    > following error message
    >
    > Undefined function 'Replace' in expression
    >
    > "Tom Wickerath" wrote:
    >
    > > You can use the Val function in conjunction with the Replace function.

    Here
    > > is an example from the Immediate Window:
    > >
    > > ?Val (Replace("1,234.567 xx",",",""))
    > > 1234.567
    > >
    > > The Replace function is needed to remove the comma, since the Val

    function
    > > recognizes only the period (.) as a valid decimal separator.
    > >
    > > In a SELECT query, you can use the following in the Field expression:
    > > NumericPart: Val(Replace([TextField],",",""))
    > >
    > >
    > > where [TextField] is the name of the field that contains your imported

    text
    > > data. Make the appropriate substitution in the name of the field. You

    can use
    > > this in the Update To: row of an Update Query:
    > >
    > > Val(Replace([TextField],",",""))
    > >
    > > Make sure that the numeric field that you are updating is defined as a
    > > Single. If it is the default Long Integer, you will only get the integer
    > > portion (1234) of the string. If the units of measure (Kg, for instance)

    are
    > > different in various records, then you'll likely want to use a different
    > > update query to update a UnitsOfMeasure field with this information.
    > >
    > >
    > > Tom Wickerath, Microsoft Access MVP
    > >
    > > http://www.access.qbuilt.com/html/expert_contributors.html
    > > http://www.access.qbuilt.com/html/search.html
    > > __________________________________________
    > >
    > > "jlambo" wrote:
    > >
    > > > I have downloaded data into a system data into a text file. I have

    imported
    > > > this data into access using a link table.
    > > >
    > > > Included in the data from the text file is data in units of measure ie
    > > > 1,234.567 kg. I have set the data type as text
    > > >
    > > > How do I convert 1,234.567 kg to a numeric (ie 1234.567)
     

Share This Page