'Right' Function

D

deiopajw

I have a field called Product.
There are items such as:
AB1000
AB400
AB1100

I want to extract only the numbers in the product.
I tried to enter the following in the source control of a field I created
to simply show the number section of the product:
=Right([Product],4)
However, product AB400 would show up as B400.
Can anyone suggest how to overcome this problem.
 
C

colin

Hi

Use the following code:

Mid([product],3,20)

where 3 is the first letter of the string that you want to extract
and 20 is the number of characters thereafter (if there are only four
characters after then the remaining 16 are ignored.

Kind regards

Colin
 
D

Dennis

If the left part is always 2 characters and it is the numeric part that
varies then use the mid function instead of the right function
=Mid([Product],3)
 
B

Brendan Reynolds

If it is always the first two characters that are non-numeric, then
Mid$([Product], 3) will get you the numeric portion.

If the number of non-numeric characters varies, you'll need a custom VBA
function.
 
D

Douglas J Steele

Actually, the 3rd parameter is optional in the Mid function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


colin said:
Hi

Use the following code:

Mid([product],3,20)

where 3 is the first letter of the string that you want to extract
and 20 is the number of characters thereafter (if there are only four
characters after then the remaining 16 are ignored.

Kind regards

Colin

deiopajw said:
I have a field called Product.
There are items such as:
AB1000
AB400
AB1100

I want to extract only the numbers in the product.
I tried to enter the following in the source control of a field I created
to simply show the number section of the product:
=Right([Product],4)
However, product AB400 would show up as B400.
Can anyone suggest how to overcome this problem.
Click to expand...
Click to expand...
 
Top