Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

This is a rushed response and not a complete answer. Firstly change your function separator from a comma to a semi-colon (Tools > Options... > LibreOffice Calc > Formula). The help page examples will make far more sense and you will be able to exchange data internationally.

This type of string parsing is best handled externally to Calc by a language like Perl, however, to obtain "RHODES" use this form of regular expression:

=LEFT(A2;SEARCH("[ ][A-Z]";A2))

...rather than your suggested:

=LEFT(A2;FIND(" ";A2;1)-1)

Overall, I would first separate your two names using something like:

=TRIM(LEFT(A2;SEARCH("[ ][&][ ]";A2))) # to give "RHODES TEDDI J" in B2


=TRIM(RIGHT(A2;(LEN(A2)-LEN(B2)))) # to give "& RALPH H JR" in C2


=RIGHT(C2;LEN(C2)-2) # to give "RALPH H JR" in D2

You can then parse the strings in B2 and D2 more easily, using the same techniques I have indicated.