Regex syntax

[Esmeralda_In_Paris]

Dear everyone, 

Hope you're all fine. 

I try to clean up a customer file. 

A field is made of such values : 

???? (one to many)

??? ??? (one to many)

??? ??? ??? 

aaa???

aaa???aaa

Ekaterina Vasilevna Krainyak

Ekaterina-Vasilevna Krainyak

Ekaterina.Vasilevna Krainyak

Ekaterina - Vasilevna Krainyak

I tried to write a regex but it covers only these 2 cases : ???? (one to many) and ??? ??? (one to many)

REGEX_Match([Field],"\?+\s?+")

thank you for your help

Esme

[Esmeralda_In_Paris]

hi Binuacs, 

How can I run this query after the previous one? the question marks have all been remove with the regex expression that Phillip wrote. 

Can you bring me some precisions ? 

thank you 

Myriam  

[binuacs]

@Esmeralda_In_Paris  I made some changes in the Regex provided by @PhilipMannering 

you will find three sections in the formula

REGEX_CountMatches([original value], '\s*\?{3,}+\s*') > 0 - checks whether the count of ? char is 3 or more if yes then use the below formula

trim((
REGEX_Replace([original value], '\s*[\?:]+\s*', ' '))
)

Else just replace single ? with a blank - ReplaceChar([original value], '?', '')

[MarqueeCrew]

@Esmeralda_In_Paris ,

Interesting data and logic.  Here is a nested expression that gets you there.  But let's express this in "English" first.  I'd love to solve it in French, but the community team discourages Google translate.

• with only ?'s and spaces (and colons), return "n/a"
• remove the set of characters and replace with a single space {?:."}
• if you have a  dash (-), make sure that there are no preceding or trailing spaces

Replace( 
	IF 
		IsEmpty(ReplaceChar([original value], "?  :", ''))	THEN "n/a"
	ELSE	
		Regex_replace(Trim(ReplaceChar([original value], "?:.", ' ')),"\s+",' ')
	EndIF
," - ", '-')

I limit the RegEx to reducing multiple spaces to a single space with:  Regex_Replace([nested logic result], "\s+", ' ')

Cheers,

Mark

[Esmeralda_In_Paris]

 you so much for your help! 

[Esmeralda_In_Paris]

thank you so much!