Junk Characters in Names List

[krishna20]

Hi Folks,

I have junk characters (different characters not a standard ones) in one of my fields and which needs to separated as first and last names into two fields.

I need the count of those (Junk rows) error records also. Please find the below sample data and advise how can i achieve this.

I have used subfield but not getting expected result.

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

Thanks

Krishna

[rwunderlich]

How do you define "Junk".  Anything that's not a letter?

-Rob

[krishna20]

Hi Rob,

I mean the junk are  like ",.12345@".

[Vegar · Accepted solution]

To filter out junk from your data you can use the PurgeChar() or KeepChar() functions.

I've used PurgeChar(Names, ',.-\1234567890@') in my attached example. 

In  a simple scenario where you only have two names separated by a blank ' ' you could use

subfield(Names, 1) as FirstName,
subfield(Names, 2) as LastName

...but in your example you have multiple names/letters with spaces between them and you will probably need a bit more complicated approach using mid() and index(). See my attached example. 

[krishna20]

Thanks Vegar :). That's worked with small modifications for my requirement.