If your data source doesn't support regular expressions:
You may want to try using trim and split:
Nested trim split statement:
TRIM( SPLIT( TRIM( SPLIT( [Blah], "(", 2 ) ), ")", 1 ) )
[Blah] is the dimension you are trying to clean up.
Nick -- Some day I need to learn REGEXP functions. That's a great solution, Nick.
If a string has two parenthetical clauses in it, would that chop out both of them?
Craig -- if your string is always in the format of "The piece of string you want to keep" followed by the parenthetical clause you want to chop out, you could use string functions.
FIND([string field], <substring value>) will give you the position of the request substring.
Then you could do a MID() function (which pulls out the specified piece of the string) or a LEFT function (which keeps x-many characters at the left of the input string.
LEFT( [address string], (FIND([address string], "(" )-1 )
would pull out from character 1 up to (but not including) the opening parenthesis. (And you would probably want to check if that LEFT function returns zero, which means the paren was not found, and then you would just want to grab the whole value of [address string].)
If you need to grab stuff after the closing parenthesis, you could do the same thing with a RIGHT function and add them together.
LEFT( ....) + RIGHT( ... )
and maybe add a space between them.
But wow. REGEXP stuff. Some day I need to learn that.
Cool replies so far.
Lots of ways to address this!
Yup, multiple solutions! Except that Joe and myself misread the question. He just wants to return address That would be easier as long as the data follows the same pattern:
TRIM( SPLIT( [Blah], " ", 1 ) )
That would take care of it. Splitting off everything before the first space. If you have a larger sample data set we can see if the above solutions need to be adjusted or not.
I'm with Joe, I always tell myself I'll learn REGEX! I never do because my stubborn side tells this can be done in Tableau and I'll eventually figure it out.
Thanks Joe, regex can be like pulling off a good card trick with a lot of failed "is this your card?" attempts.
The one I posted would remove all instances of parentheses groups. It didn't remove inner parens but with a small tweak, a "*" after the group and it does. Not the Craig asked for that, but just some more fun with regex.