Share via

How to convert text to numbers in Access

Anonymous
2010-12-03T16:50:52+00:00

I need to convert text data stored in a field from this format 00000-0000-00 to:

  1. 00000000000 (without the "-"),
  2. just a number without leading zero.

For example: if the field contains 00059-1234-01, I want to convert to a field to show 00059123401 and another field to show 59123401. Can you help?

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-12-03T17:01:54+00:00

    A combination of the Replace() and Int() functions should work:

    Int(Replace(<TheField>, "-", ""))

    -- Roger Carlson

    MS Access MVP 2006-2010

    www.rogersaccesslibrary.com

    5 people found this answer helpful.
    0 comments
  2. Anonymous
    2010-12-03T19:47:43+00:00

    Format will also work:

    Format([TheField])

    Format([TheField], "00000000000") will give you the leading zeros back (00059123401).

    -- Roger Carlson

    MS Access MVP 2006-2010

    www.rogersaccesslibrary.com

    2 people found this answer helpful.
    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2010-12-03T17:02:43+00:00

    Create a query based on the table.

    Let's say the field is named MyField.

    Create a calculated column

    JustDigits: Replace([MyField],"-","")

    and another one

    RealNumber: Val(Replace([MyField],"-",""))

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2010-12-03T19:20:53+00:00

    CStr([YourNumberField])

    Build a little, test a little.

    0 comments
  5. Anonymous
    2010-12-03T19:16:04+00:00

    Thank you so much, Roger and HansV! They work!!! This saves me hours of searching in the Help section.

    One more thing, how do I convert the real number to text format in a query? ie 59123401 as text and not as a number.

    0 comments