I would like a dictionary that replaces one specific string with another, but any other string should be replaced by -BLANK-.  I think this is easy but I can't figure it out!

RD101 -> recurring

-> -BLANK-

Well, it lost my string in brackets.

Should say:

RD101 -> recurring
anything else -> -BLANK-

Well, that worked, but can you explain what is happening in that regular expression?

Hi Wayne,

Amanda asked me to help with that regex so it's probably best if I explain it.

The first set is pretty clear. Just replacing the RD101 to recurring.

The second set can be confusing but what is really going on here is a regex IF statement. It works like this:

(?(expression)yes|no)

If the expression matches then used the regex expression in the yes area, otherwise use the no area. In your case we are matching on "recurring" as our expression, and if it matches, then use MATCHED as the expression to replace against (MATCHED is unlikely to actually match anything in your column so I figured it was a good expression to use). So whats happening is every entry that doesn't contain "recurring" is getting the .* expression (.* is any character zero or more times, essentially any value) and being replaced with blank.

So why don't I come out with "MATCHED" instead of recurring?

If the text to compare contains "recurring" then it would try to replace the expression "MATCHED" with blank. I chose "MATCHED" because that is unlikely, you can change it to some other unlikely to match expression.

If the text to compare does not contain "recurring" then it would try to replace the expression ".*" with blank.

Here's a simple example:
Replacement Value: #

Value to match on: (?(.*a.*)b|c)

Text to test against: banana
Result: #anana
"b" is replaced in banana because banana contains "a"

Text to test against: cup
Result: #up
"c" is replaced in cup because cup doesn't contain "a"

Great, got it.

You are the best Nic!

I just created a Regular Expression that is related to this one, so I thought I would post it:

I needed to populate Marital Status on the primary constituent's record during the import but I did not have a column for this value in my data file. Since the file is a mix of married and single people, I couldn't simply apply a Static Field to handle Marital Status. I didn't want to split my file and do a two pass import, nor did I want to add the column to my data file.

I decided to use a Virtual Field with the Copy Field function and the Seed = Column containing Individual Relationship Last Name, for Marital Status. I then attached the below RegEx dictionary to get the desired values.

Dictionary:

Two entries.

Turn on "Use Regular Expressions" for both Replacement Values.

 

#1 Replacement Value = Single

#1 Value To Match On = ^\s*$     

 

#2 Replacement Value = Married

#2 Value To Match On = ^\S+$    

 

The layman's terms of what the RegEx is doing:

If the field is BLANK then translate to Single.

If the field is NOT BLANK, regardless of the value, then translate it to Married.

 

Hope someone finds this useful!

Tiffany

Thanks, Tiffany! That's exactly what I came looking for! Doing the same thing with marital status and checking the spouse checkbox!

I have come across another way to get the elusive "match anything except X" to work by using the negative lookahead function.

The expression looks like:^(?!(regex)$).* where regex  is any valid regular expression.

For example, if you wanted to make anything that was not "Male" or "Female" become "Unknown" you can use:
Replacement Value: Unknown
Value to Match: ^(?!(Male|Female)$).*

Or, to recreate my original request in this thread I could do
Replacement Value1: recurring
Value to Match1: RD100

Replacement Value2: -BLANK-
Value to Match2: ^(?!(recurring)$).*

I had an issue where I was adding an education relationship in most cases, but some of the people in the input file did not have the school specified. If the school was there, I wanted the status to fill in with a certain text, but if the school was not there, status should be blank. I ended up creating a Dictionary entry with the Replacement value being my "Blah Alumnus" string and the values to match on being the RegEx expression below. This matches anything with or without spaces.

^(?!\s*$).+

^ anchors the search at the start of the string.

(?!\s*$), a so-called positive lookahead, asserts that it's impossible to match only whitespace characters until the end of the string.

.+ will then actually do the match. It will match anything (except newline) up to the end of the string.

Doug, welcome to the forums!

If you are just looking to say: "If there is something in the field then make it 'Blah Alumnus' but if it is blank then leave it blank"

could you just do:
Replacement: Blah Alumnus
Value to Match: .+

Then it you match any characters and replace it with the string, or match no characters and just leave it blank.

I'm trying to set up a dictionary with several values. If the text in the field does not match any of the values, then it should say "General".
Some of the values have spaces, and some do not. I tried Tiffany's expression, but it does not work if there are spaces.
Can someone help me figure this out?
Thanks!

Assume that I want to replace everything but "one" and "two words" with a blank

Replacement Value	Value to Match
VALID$1	^(one)$
	^(two words)$
	...CONTINUE LIST...
$2	^(VALID|.*)(.*)

make sure to click "use regular expressions"

Explained

The first part VALID$1 and ^(one)$ will add "VALID" to the beginning of a value that matches "one".
So after step 1 the value is now VALIDone.  This is repeated for all of the values that you enter in here making sure to enclose the whole value in parentheses. 

The second part $2 and ^(VALID|.*)(.) will put either VALID or .* (the whole string) into $1 and then what is left over in $2.  The key here is that if the string does not start with "VALID" then all of the string goes into $1.  Only if the string starts with "VALID" will there be anything left over in $2.
After step 2 the value is now back to one since it had been appended a VALID in step 1.  If you try to type "two" then that will not get a VALID from step 1 and so will be blank after step 2

Give it a shot!