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

Regex

Preparation

Accepted answers

philipmannering

Hi @Esmeralda_In_Paris

Perhaps this works?

regex-syntax.yxmd

philipmannering

And I forgot to include this, but you can then replace empty cells with n/a using

if isempty([correction]) then 'n/a' else [correction] endif

Esmeralda_In_Paris

Dear Philip,

thank you so much, the regex is working fine.

could you please explain this expression?

another request :

when the name is made of 2 words, how can I keep the space between the 2 words and remove them when they are inside a word?

Example :

original field Correction

gül?ah gülah

HAN PA?ABEYO?LU Han Paabeyolu

thank you very much

Esme

binu_acs

@Esmeralda_In_Paris for the second request you can use the ReplaceChar function like below

All comments

JarekSkudrzyk

@Esmeralda_In_Paris hi,

please confirm if my understanding is correct:
in your dataset the same name can be written in various ways (e.g. with ".", "-" between names or without any punctuation) and you would like them to be all unified as one name?

JarekSkudrzyk

if this is the case, please find sample solution

regex.yxmd

Esmeralda_In_Paris

hi Jarek,

thank you for your help

your regex is considering the last three examples, but I also have a lot of these values (real question marks)

???? : could be one question or a various number of question marks without space in between

??? ??? : could be one question or a various number of question marks with space in between

??? ??? ??? : could be one question or a various number of question marks with space in between

aaa??? : a series of letters (either caps or small letters) followed by question marks

aaa???aaa : a series of letters (either caps or small letters) followed by question marks and followed by letters

thank you

binu_acs

@Esmeralda_In_Paris Can you please provide the expected result based on your input?

Esmeralda_In_Paris

Hi Binuacs,

here are the mapping I'd like to apply

original value correction

???? n/a

??? ??? n/a

??? ??? ??? n/a

aaa??? : aaa

aaa???aaa : aaa aaa

Ekaterina Vasilevna Krainyak Ekaterina Vasilevna Krainyak

Ekaterina-Vasilevna Krainyak Ekaterina-Vasilevna Krainyak

Ekaterina.Vasilevna Krainyak Ekaterina Vasilevna Krainyak

Ekaterina - Vasilevna Krainyak Ekaterina-Vasilevna Krainyak

thank you so much

Esme

philipmannering

Hi @Esmeralda_In_Paris

Perhaps this works?

regex-syntax.yxmd

philipmannering

And I forgot to include this, but you can then replace empty cells with n/a using

if isempty([correction]) then 'n/a' else [correction] endif

Esmeralda_In_Paris

Dear Philip,

thank you so much, the regex is working fine.

could you please explain this expression?

another request :

when the name is made of 2 words, how can I keep the space between the 2 words and remove them when they are inside a word?

Example :

original field Correction

gül?ah gülah

HAN PA?ABEYO?LU Han Paabeyolu

thank you very much

Esme

binu_acs

@Esmeralda_In_Paris for the second request you can use the ReplaceChar function like below

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

binu_acs

@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], '?', '')

regex-syntax.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703