Parsing text out of a description

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

I am not sure if there is anyway to do this but.

I have a description field that has data like the following:
Report id FBB3288-01, Then other info
Report id FBNR002C; Then other info
Report id FBNR00BV Then other info

(Notice sometimes it ends in a comma, or semi or space)

I want to Parse out after the Report Id just the Report # and I do not want
Report ID text or the other text after the Report #.
FBB3288-01
FBNR002C
FBNR00BV

Thanks
 
Heather said:
I am not sure if there is anyway to do this but.

I have a description field that has data like the following:
Report id FBB3288-01, Then other info
Report id FBNR002C; Then other info
Report id FBNR00BV Then other info

(Notice sometimes it ends in a comma, or semi or space)

I want to Parse out after the Report Id just the Report # and I do not want
Report ID text or the other text after the Report #.
FBB3288-01
FBNR002C
FBNR00BV
Click to expand...


If you have rules that can be guaranteed to identify the
part you want, then it's possible. For example, if the
desired part always starts with FB and FB never occurs
anywhere else in the field, then you can use the InStr
function to locate the FB and the following space and go
from there.
 
This finds the beginning of the Report number
Instr(1,[SomeField], "Report ID ")+11

This chops off the beginning
Mid([SomeField],Instr(1,[SomeField], "Report ID ")+11)

This chops off the end except for the comma and semi-colon or other
punctuation marks.

Left(Mid([SomeField],Instr(1,[SomeField], "Report ID ")+11),
Instr(1,Mid([SomeField], Instr(1,[SomeField], "Report ID ")+11)," ")-1)

Then you can either use the replace function to strip off commas and
semi-colons or some more testing of the last character.

Replace(Replace(Left(Mid([SomeField],Instr(1,[SomeField], "Report ID ")+11),
Instr(1,Mid([SomeField], Instr(1,[SomeField], "Report ID ")+11),"
")-1),",",""),";",""))

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