asked on

Extract single character from substring

Dear Experts:

I would like to extract the single characters in these cells using a formula:

Prof. Dr. med. John F Kennedy, i.e. F has to be extracted
Dr. Andrea Skan, i.e. nothing will be extracted
Prof. John B Sutherland: B will be extracted
James D Sucker, i.e. D will be extracted
etc.

The bolding is just for emphasis, in reality there is no bolding.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Benjamin Lu

Please try to use array formula:

=IF(TRIM(RIGHT(A1,2))=RIGHT(A1,1),RIGHT(A1,1),IFNA(MID(A1,MATCH(1,IF(LEFT(MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-3,0)),3),1)&RIGHT(MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-3,0)),3),1)=" ",1,0),0)+1,1),""))
D may in the middle or at the end, if it's at the begin, you need to add up follow formula:
& IF(TRIM(Left(A1,2))=Left(A1,1),left(A1,1),"")

Please remember to press CTRL+ALT+ENTER to enable the formula
sample.xlsx

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

I am really overwhelmed by this kind of support. Thank you very much for it. Bear with me some time till I can go thru all this.

Thank you very much again for your great support.

Andreas Hermle

ASKER

Saqib: As byundt already said, Saqib' solution is really great. Super!!Thank you very much for it.

byundt: your codes will come in handy in other cases. Thank you very much for it.

Benjamin: I am afraid to tell you that your formula does not work on my side. It is throwing error messages. And moreover Saqib's code is more concise. Anyhow thank you very much for the time taken and your professional efforts. I really appreciate it.

Regards, Andreas