part of string

  • Thread starter Thread starter Claes D
  • Start date Start date
C

Claes D

Hello all.

I've been trying and trying, but I can't get it to work.

I have
abc_12345678_1234.abc
abcd_12345678_1234.abc
abcde_12345678_1234.abc

I need 1234 (4 digits) from the last part. (After the second underscore)

How do I do this.
I have tried mid-function, but obviously I am doing something wrong with it.
:)

Grateful for any help.
 
Assumption:
The field always has two underscore characters

Mid(TheField,Instr(Instr(1,TheField,"_") +1,TheField,"_")+1,4)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
If all your data end with the 4 digits, a period, and 3 characters then use
this ---
Left(Right([YourField], 8),4)

OR if there is only one period and it follows your 4 digits use this ---
Left(Right([YourField], Instr([YourField], ".")+4),4)

(might need to be +5)
 
Back
Top