Is there a possibility of nesting if-statements whereby the criteria is the same but the data may vary? I prefer to segregate a full name string into parts of the full string and place them into individual respective fields.
Example - splitting first name and last name from a combined name field:
Expected outcome: First Name 1: John ; Middle Name 1: M. ; Last Name 1: Doe ; Suffix 1: (null) ; First Name 2: Jane ; Middle Name 2: (null) ; Last Name 2: Wright ; Suffix 1: (null)
1) Original data: John M. Doe And Jane Wright
2) Original data: John M. Doe & Jane Wright
3) Original data: John M. Doe In C/O Jane Wright
I was only able to use RegexIsMatch() and LSplit() functions for one criteria. I prefer to code several combinations of the search criteria into one long statement if possible and split the names accordingly.
Below is code I started with while removing any names containing a company name:
If((RegexIsMatch(LTrim(Full Name)," [&] ") .Or. RegexIsMatch(LTrim(Full Name)," [Aa][Nn][Dd] ") .Or. RegexIsMatch(LTrim(Full Name)," [Cc]/[Oo] ") <>0) .And. ((RegexIsMatch(LTrim(Full Name),"[^Ll][^Ll][^Cc]")) .Or. RegexIsMatch(LTrim(F5),"[^Ii][^Nn][^Cc]") .Or. RegexIsMatch(LTrim(F5),"[^Cc][^Oo][^Rr][^Pp]*"))<>0 , LSplit(LTrim(F5),5,"&",1) ,'')
This only works for one match, but it does not split them into individual fields as the example above. I am only able to pull just the first section of the full name string. I need it to work for a variety of matches into a single-coded statement. Please advise. Thanks.