Parsing text out of a description

[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

 

[Marshall Barton]

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

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.

 

[John Spencer MVP]

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

 

[MGFoster]

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT Trim$(Replace(Replace(Replace(Left$(description,InStr(Len("Report
id ")+1,description, " ")),"Report id ", ""),",",""),";", "")) As
ReportNbr, ... etc. ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdpa44echKqOuFEgEQKonQCgmaHcjOQRUvJaN2sCvnA7QvGgS4IAnimM
Ug7vQC/3HxRP5s1kq4cEKuYU
=P8Kv
-----END PGP SIGNATURE-----