I have strings of text which are file locations within a worksheet
e.g "\Clinical_Audit\Projects\Project Monitoring\Audit\0910\CAMHS\02
Improving access to appropriate services for children, young people and their
families\"
I want to extract the service code from the string (ins this case "CAMHS")
and can do for this row using
=MID(D3,56,5)
But some of the service directories (all begin at char 56) are the same
length (5) others have 4 char some 2 etc.

ANy ideas who to acheive this?

THanks
Trevor

=LEFT(MID(A1,56,999),FIND("\",MID(A1,56,999))-1)

We get EVERYTHING from 56 to the end and then look for the first separator.
--
Gary''s Student - gsnu200909


"Trevor Aiston" wrote:

I have strings of text which are file locations within a worksheet
e.g "\Clinical_Audit\Projects\Project Monitoring\Audit\0910\CAMHS\02
Improving access to appropriate services for children, young people and their
families\"
I want to extract the service code from the string (ins this case "CAMHS")
and can do for this row using
=MID(D3,56,5)
But some of the service directories (all begin at char 56) are the same
length (5) others have 4 char some 2 etc.

ANy ideas who to acheive this?

THanks
Trevor

Change your formula to this:

=MID(D3,56,FIND("\",D3,56)-56)

This looks for the next \ after the 56th character to determine how
many characters to extract.

Hope this helps.

Pete

On Nov 26, 12:06*pm, Trevor Aiston
wrote:
I have strings of text which are file locations within a worksheet
e.g "\Clinical_Audit\Projects\Project Monitoring\Audit\0910\CAMHS\02
Improving access to appropriate services for children, young people and their
families\"
I want to extract the service code from the string (ins this case "CAMHS")
and can do for this row using
=MID(D3,56,5)
But some of the service directories (all begin at char 56) are the same
length (5) others have 4 char some 2 etc.

ANy ideas who to acheive this?

THanks
Trevor

do you wish to achieve this:

=MID(D3;FIND("0910\",D3)+5,LEN(D3)-FIND("0910\",D3)-5)

?


On 26 Lis, 13:06, Trevor Aiston
wrote:
I have strings of text which are file locations within a worksheet
e.g "\Clinical_Audit\Projects\Project Monitoring\Audit\0910\CAMHS\02
Improving access to appropriate services for children, young people and their
families\"
I want to extract the service code from the string (ins this case "CAMHS")
and can do for this row using
=MID(D3,56,5)
But some of the service directories (all begin at char 56) are the same
length (5) others have 4 char some 2 etc.

ANy ideas who to acheive this?

THanks
Trevor