Pulling data from the middle of a field

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have a field that has 6 digits in the middle of a string of data
(EA6BSDLM/161232/SeqNbr1X50) that I need to isolate in a report. How can I
trim off the left and right side of the field, when there is no uniformity in
the data entry.
 
Hi Jim,

Assuming the slashes always surround the 6 digits, try:

'Get rid of the front
strMyDigits = Right(myField, (Len(myField) -Instr(myField,"/")+1))
'Get rid of the back
strMyDigits = Left(myField,6)

You can combine them as:
strMyDigits = Left(Right(myField, (Len(myField) -Instr(myField,"/")+1)),6)

Regards

Kevin
 
Assumption there is always a slash "/" before the six digits AND there is no
other slash in the entry before the slash

Mid([SomeField],Instr(1,[SomeField],"/")+1,6)

If there is no pattern other than six consecutive number characters in the
string then you will need to write a custom VBA function or use a regular
expressions (regex) to extract the informatioin.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top