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

[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

[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 

[binuacs]

@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 · Accepted solution]

Hi @Esmeralda_In_Paris 

Perhaps this works?

[PhilipMannering · Accepted solution]

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 · Accepted solution]

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

[binuacs · Accepted solution]

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