I am trying to extract name, firstname, lastname, create nickname/firstname, and nickname/lastname from name list that have acronyms, aristocratic title, academic titles/degrees, nickname inside parentheses or after slash-backslash in libreoffice calc using regex function. This is the expected result
The rules are:
- Expected Name: remove nickname inside parentheses or after slash-backslash if exist, job titles, academic titles and degrees except for (R) or (R.), (Ra) or (Ra.), and one alphabet with(out)
dotin the name ex: (A.) (A), (M.), and name acronym with(out)dotex: (Muh.), (Moh), etc - Firstname: (from expected name) extract first name including
dotin acronym ex: (R.), (Ra.), (Muh.) and one alphabet with(out)dotin the name ex: (A.) (A), (M.) - Lastname: (from expected name) extract last name including
dot - Nickname-fname: from Input Name, extract nickname inside parentheses or after slash-backslash if exist. If not exist, extract first name that is not: one char, acronym with(out)
dot, "I Gede", "I Gusti", "I Made", "Ni Luh Putu", or "Ni Putu". If not exist, use next word in name even it's last word. If the name consist only one word, extract it even only one character - Nickname-lname: from Input Name, extract nickname inside parentheses or after slash-backslash if exist. If not exist, extract last name that is not: one char or acronym with(out)
dot. If not exist, extract prev word in name even it's first word. If the name consist only one word, extract it even only one character
I tried the following:
=REGEX($A2,"\(.*?\)|\([^)]*\)|\\[^\\]*$|\/[^\/]*$|,.*","")
to extract real name. It remove nickname inside parentheses or after slash-backslash with(out) space like
( Nita ) in Yunita ( Nita )
( Nita ) in Yunita( Nita )
(Nita) in Yunita (Nita)
(Nita) in Yunita(Nita)
( Nita) in Yunita ( Nita)
( Nita) in Yunita( Nita)
(Nita ) in Yunita (Nita )
(Nita ) in Yunita(Nita ))
\Nita in Yunita\Nita
\ Nita in Yunita\ Nita
\Nita in Yunita \Nita
\ Nita in Yunita \ Nita
...
and so on
it also remove academic degrees like Ph.D. but it failed on Ra. Ayu S. Ph.D. (because there is no comma after S.). It failed on Prof. and Dr.. I want to keep Ra..
=REGEX($A23,"(?:^|(?:[.!?]\s))([\w.]+)")
to extract first name but failed on Moh.Ali (yes, without space after dot).
=REGEX($A23,"\b(?<last>[\w\[.\]\\]+)$")
to extract last name but also failed on Moh.Ali.
=REGEX($A2,"(\(.*?\)|\([^)]*\)|\\[^\\]*$|\/[^\/]*$)|(\b(?<first>\w+)$)")
to create nickname extracted from given nickname inside parentheses or after slash or backslash. If nickname not exist create one from first name that is not: one char, acronym with(out) dot, "I Gede", "I Gusti", "I Made", "Ni Luh Putu", or "Ni Putu". If the first name not meet condition, use next word in name even it's last word. If still not meet condition, use name that consist only one word, extract it even only one character. The regex failed in most case.
=REGEX($A2,"(\(.*?\)|\([^)]*\)|\\[^\\]*$|\/[^\/]*$)|(\b(?<last>\w+)$)")
to create nickname extracted from given nickname inside parentheses or after slash or backslash. If nickname not exist create one from last name that is not: one char, acronym with(out) dot. If the last name not meet condition, use prev word in name even it's first word. If still not meet condition, use name that consist only one word, extract it even only one character. It failed in most case too.
This is the result of the regex:
But I'm new to regex and stuck. Please help me