Beginner seeks help with IF statement

[Ian]

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

 

[Andrea Jones]

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]

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

Hi Andrea,

Many thanks for your help. I've managed to get the formula for column P
working but not the formula for Q, are the cell references correct for
the Q cell formula ?

I was also wondering if it's possible for the cells in row L to be
blanked after they formulas for columns P and Q have been carried out,
i.e. if column L contains the words "son of", "dau of" or "wife of" it
gets cleared but only after the formulas for columns P and Q have been
populated.

Best wishes,

Ian

 

[Andrea Jones]

Oops! Sorry, I forgot to replace the A2s with your cell refs, the formula in
Q1 should be

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

Andrea Jones

 

[Ian]

Thanks Andrea. Appreciate your help.

Oops! Sorry, I forgot to replace the A2s with your cell refs, the formula in
Q1 should be

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

Andrea Jones