Special Characters has to be removed from Name Field

krishna20 — Thu, 23 Dec 2021 18:27:05 GMT

Hi Folks,

I have to remove the special characters from the name field and the below are the conditions which are need to be considered. I need the count of those errors.Please find the below sample data and advise how can i achieve this.

Error Count

At the begining and ending should not have special characters.
Inbetween first and last name should not have special character (other than .,)
There are email ID also there in the data instead of names those can be considered as Error.

not Error count

., can have in the first and last name separation.

Names

, Amit Agrahari

, Anuradha Iyer

, Diamond Gong

, Hemant P. Deore

, Jules Keghie

, Vishal Prasad,

. Deepan.

. Harshana

. Martijn

\Remy Alexander

21 New Interns

A D A Ratnam

A.K. Pandey

T.Uday

Expected Result as below

Names	First Name	Last Name	Error Count
, Amit Agrahari	Amit	Agrahari	1
, Anuradha Iyer	Anuradha	Iyer	1
, Diamond Gong	Diamond	Gong	1
, Hemant P. Deore	Hemant P.	Deore	1
, Jules Keghie	Jules	Keghie	1
, Vishal Prasad,	Vishal	Prasad	2
. Deepan.		Deepan	2
. Harshana		Harshana	1
\Remy Alexander	Remy	Alexander	1
21 New Interns	New	Interns	1
A D A Ratnam	A D A	Ratnam	0
A.K. Pandey	A.K.	Pandey	0
.,Uday Krishna*	Uday	Krishna	3

Regards

Kalyankrishna T

Re: Special Characters has to be removed from Name Field

Anil_Babu_Samineni — Mon, 20 Jan 2020 16:59:07 GMT

Perhaps this?

LOAD Names, Names1, SubField(Trim(PurgeChar(Names1,'0123456789')),' ',1) as FName, SubField(Names1,' ',-1) as LName;
LOAD Names,
If(Match(Left(Names,1),',','.','\', '*') or Match(Right(Names,1), '.', '\', ',', '*'), Trim(PurgeChar(Names,',.\*')), Names) as Names1 Inline [
Names
", Amit Agrahari"
", Anuradha Iyer"
", Diamond Gong"
", Hemant P. Deore"
", Jules Keghie"
", Vishal Prasad,"
". Deepan."
". Harshana"
". Martijn"
"\Remy Alexander"
"21 New Interns"
"A D A Ratnam"
"A.K. Pandey"
".,Uday Krishna*"
];

topic Special Characters has to be removed from Name Field in App Development

Special Characters has to be removed from Name Field

Re: Special Characters has to be removed from Name Field