Extract Text ? (Excel 2002)

Topic

New Reply

Hi

I have been able to extract salutation from C2 but I can’t select the Initial.

Please see sample attached.

Many Thanks

Braddy

Reply | Quote

Replies

You could use:

=MID(C2,FIND(" ",C2)+1,1)

This will allow for salutaions longer than 2 characters, if the initial is always the 4th character then you could use:

=MID(C2,4,1)

Reply | Quote

Hi Tony

Thanks for the reply, I have discovered a problem with my formula in A2 it only brings in Mr where it will need to bring in Mrs Or Miss in some cases.

If I could impose on you, after I Have in A2 Mr and B2 D then I would like to be able to remove them from C2 leave just Stephens.

Thanks to all who replied

Braddy

Reply | Quote

Salutation

=LEFT(C2,FIND(" ",C2)-1)

Initial

=MID(C2,FIND(" ",C2)+1,1)

Surname

=RIGHT(C2,LEN(C2)-FIND("*",SUBSTITUTE(C2," ","*",LEN(C2)-LEN(SUBSTITUTE(C2," ","")))))

(I think I got the last method from a John Walkenbach book)

Reply | Quote

Hi Tony

Thanks very much, everything works just fine,

Once again thanks to everyone who replied.

Braddy

Reply | Quote

For middle name put (checks to see whether there is a middle name):

=IF(ISERR(MID(A1,FIND(” “,A1)+1,IF(ISERR(FIND(” “,A1,FIND(” “,A1)+1)),FIND(” “,A1),FIND(” “,A1,FIND(” “,A1)+1))-FIND(” “,A1)-1)),””,MID(A1,FIND(” “,A1)+1,IF(ISERR(FIND(” “,A1,FIND(” “,A1)+1)),FIND(” “,A1),FIND(” “,A1,FIND(” “,A1)+1))-FIND(” “,A1)-1))

Reply | Quote

Hi Shades

Took my breath away a bit, but I will give it a go.

Many Thanks

Braddy

Reply | Quote

I think it comes from J. Walkenbach.

I know I use it frequently.

Reply | Quote

If you have salutations consisting of more than one word such as “Mr or Ms” and “Prof Dr” etc., it is hard to do with a formula, because you cannot just search for the first space. Also, you cannot use a formula to change C2 itself, because the moment you do that, A2 and B2 will become invalid. So it would be better to use VBA code, but you will need to provide a complete list of all possible salutations.

Reply | Quote

Braddy

In addition to Tony’s answer to find an initial from the string use:

=MID(C2,LEN(LEFT(C2,FIND(” “,C2)+1)),1)

This wiil work for all titles: Mr Mrs, Miss, Prof. etc

Reply | Quote

LEN(LEFT(C2,x)) = x by definition of the functions involved. So there is no need to use LEN and LEFT.

Reply | Quote

Yep…thanks

Reply | Quote