Query that takes a portion of a string and moves it to a new field

[b4btec]

I have a field that some bright person decided to use as two fields. Basically, this field may have any number and type of characters (or none) before a string that starts has NMFC#. The substring that starts with NMFC# always goes to the end of the field. Basically what I need to do is take a substring of NMFC# and everything after it and move it to a new field in the table, leaving the random strings in the original field. What is the best way to accomplish this?

A sample of the data in this field:

50#/CS NMFC# 151320

50#/CS NMFC# 151320

50#/CS NMFC# 151320

500#/DRUM NMFC# 4610

500/rl NMFC#153500-2

500405 NMFC# 154675

500516 NMFC# 154675

[JoeM]

You can do a calculated field in a query (and just leave it as that).
You could write it back to a table field if you really want to, but that usually isn't necessary, since you can use a Query for pretty much anything that you use a Table for.

Here is what that calculated field would look like:

MySubstring: Mid([FieldName],InStr([FieldName],"NMFC#"))