Extracting part of a text value

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

I have a text value that ranges from 5 to 9 characters. The last two
characters are always two letters. All of the other characters are always
numbers. I want to separate the letters from the numbers - keeping the
numbers. I do not need the letters.

The "Left", "Right", and "Mid" expressions don't get me what I need.

How can I extract just the numbers?

Thanks in advance!
 
As long as the field is always at least 3 characters in length the following
should work.

Left(YourField, Len(YourField)-2)

If you wish you can test
IIF(Len([YourField] & "")> 3, Left([YourField], Len([YourField])-2), Null)

IF you want to return whatever is in your your field if it has 2 or fewer
characters than replace null with [YourField]


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top