CODEWITHSUNDEEP
stringms-accessexpression
RARascon
RARascon

Reputation: 129

Access Query Expression: Extract word between two other words

Hi there: I've checked several solutions now and I'm close but not quite there. However, I'm also trying to determine if what I'm doing makes sense. I just keep feeling like there's a better option.

Business problem: I need to extract the Region Name, which is between the word "DID " and " Region". I've been able to do either one to get everything before and the Region Name and the word "Region", but I want just the Region Name. Please see below and advise - very much appreciated!

Example: DID North Region. I only want "North".

Region: IIf(Right([Tier 6],6)="Region",Mid([Tier 6],InStrRev([Tier 6],"DID ")+4),"")

This expression gives me [Region Name] Region. I want the word "Region" removed as well.

Thank you!

Upvotes: 0

Views: 384

Answers (1)

June7
June7

Reputation: 21370

Since you are using IIf conditional, I presume "Region" is not always present. Also presume if there is no "Region" then there is no "DID". In case there is possibility field will be null, consider:

Region: IIf(InStr(Nz([Tier 6],""),"Region")>0,Mid(Left([Tier 6],InStrRev([Tier 6]," ")-1),5),[Tier 6])

Upvotes: 1

Related Questions