You can do this with embedded IFs and text functions like LEN and MID to take
the 'son of' part out of the string. Your formula in P1 would be something
like:

=IF(MID(L1,1,3)="son","so",IF(MID(L1,1,4)="wife"," wi",IF(MID(L1,1,3)="dau","da","")))

The formula to use in Q1 would then be

=IF(OR(B2="so",B2="da"),MID(A2,8,LEN(A2)-7),IF(B2="wi",MID(A2,9,LEN(A2)-8),""))

Andrea Jones

"Ian" wrote:

Can anyone help me with the following please.

I have a spreadsheet that has textual data in one column describing the
relationship of the person, for example "son of John". What I want to
be able to do is process all the rows for this particular column as
follows:

If a cell in column L contains the fields "son of", "dau of" or "wife
of" I want the cell in column P to have inserted "so", "da" or "wi"
respectively. Then I would want column Q to have inserted the name of
the person to whom they are related and then finally the original cell
to be emptied.

So, the following:

Before
L1: son of John Henry

After
L1:
P1: so
Q1: John Henry


and


Before
L1: wife of William

After
L1:
P1: wi
Q1: William


and


Before
L1: dau of George Herbert

After
L1:
P1: da
Q1: George Herbert


Any help would be much appreciated.

Ian