Results 1 to 12 of 12
Thread: Extract Text ? (Excel 2002)

20051109, 10:42 #1
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Extract Text ? (Excel 2002)
Hi
I have been able to extract salutation from C2 but I can't select the Initial.
Please see sample attached.
Many Thanks
BraddyIf you are a fool at forty, you will always be a fool

20051109, 10:58 #2
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Extract Text ? (Excel 2002)
You could use:
<code>=MID(C2,FIND(" ",C2)+1,1)</code>
This will allow for salutaions longer than 2 characters, if the initial is always the 4th character then you could use:
<code>=MID(C2,4,1)</code>

20051109, 11:22 #3
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Extract Text ? (Excel 2002)
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. etcJerry

20051109, 11:29 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Extract Text ? (Excel 2002)
LEN(LEFT(C2,x)) = x by definition of the functions involved. So there is no need to use LEN and LEFT.

20051109, 11:36 #5
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Extract Text ? (Excel 2002)
Yep...thanks <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
Jerry

20051109, 12:03 #6
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Extract Text ? (Excel 2002)
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
BraddyIf you are a fool at forty, you will always be a fool

20051109, 12:28 #7
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Extract Text ? (Excel 2002)
Salutation
<code>=LEFT(C2,FIND(" ",C2)1)</code>
Initial
<code>=MID(C2,FIND(" ",C2)+1,1)</code>
Surname
<code>=RIGHT(C2,LEN(C2)FIND("*",SUBSTITUTE(C2," ","*",LEN(C2)LEN(SUBSTITUTE(C2," ","")))))</code>
(I think I got the last method from a John Walkenbach book)

20051109, 12:32 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Extract Text ? (Excel 2002)
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.

20051109, 13:21 #9
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Extract Text ? (Excel 2002)
Hi Tony
Thanks very much, everything works just fine,
Once again thanks to everyone who replied.
BraddyIf you are a fool at forty, you will always be a fool

20051109, 17:39 #10
 Join Date
 Jan 2002
 Location
 Missouri, USA
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract Text ? (Excel 2002)
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))

20051109, 18:27 #11
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Extract Text ? (Excel 2002)
Hi Shades
Took my breath away a bit, but I will give it a go.
Many Thanks
BraddyIf you are a fool at forty, you will always be a fool

20051109, 20:11 #12
 Join Date
 Jan 2002
 Location
 Missouri, USA
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract Text ? (Excel 2002)
I think it comes from J. Walkenbach.
I know I use it frequently.