Replace Function

[razkowski]

Hello-



I'm trying to use the replace function in an access query to find and replace certain characters. The data in the field is: (Unit #2436; Contract #03-36-036801).

I only want the 10 digit code with the dashes: 03-36-036801. So, I need to remove "(Unit #2436; Contract #" and ")" at the end.

How do I go about this?

Thanks

[JoeM]

Do all you entries ends with a ")", and are the 10 digits you want always the last 10 characters before this ")"?
If so, you can use the MID and LEN functions to get what you need:
MID([FieldName],LEN([FieldName])-12,12)

[razkowski]

Sometimes there will be characters after the last ")".

[JoeM]

OK, but is it always the 12 characters previous to the ")"? If so, then this will work:
Mid([FieldName],InStr([FieldName],")")-12,12)

[razkowski]

99% of the time, yes. However, I've just realized that on rare occasions, there are additional characters before the last ")" that I do not wish to include. Like this: (Unit 46; Contract #03-11-003831-47-35)

[June7]

Why is multiple discrete data in the same field to begin with?

You want the 12 characters following the second # character (oh wait, second example has only 1 #)? Will "Contract #" always be in the string? There will never be a space following the #?

Consistency is critical with string manipulation.

x represents your field:

Mid(x, InstrRev(x, "#") + 1, 12)

[razkowski]

June7 - your response did the trick. Works perfectly. To answer your question, because that's the way the data comes from our vendor. We have tried numerous times to get them to report in a fashion that is more suitable to us. It hasn't happened. So, plan B is to extract the data that is meaningful to me from all their nonsense data. That's what you just accomplished for me.

Thanks

[JoeM]

As you can see, the "trick" is usually identifying the pattern/rule to the data you are working. That is the first thing you want to identify, all the possible scenarios. Then from there, you can create a formula to do that.