Hi,

I would like to extract a section of text from a list of drawing numbers.

For example ....

LEFT\ADV CONT\S3JL081P0251 REV D.DWG

LEFT\ADV CONT\S3JL081P0252 REV E.DWG

LEFT\ADV CONT\S3JL081P0253 REV D.DWG

LEFT\ADV CONT\S4JL081P0254 REV E.DWG

LEFT\ADV CONT\S4JL081P0255 REV F.DWG

LEFT\ADV CONT\S4JL081P0256 REV F.DWG



The part I want to extract is anything inclusive from "S3" or "S4" to the
end of the text string but not including the ".dwg" part

Please could someone help me with a function string to do this ?

Thankyou

=MID(A1,15,18)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"someone" wrote in message
...
Hi,

I would like to extract a section of text from a list of drawing numbers.

For example ....

LEFT\ADV CONT\S3JL081P0251 REV D.DWG

LEFT\ADV CONT\S3JL081P0252 REV E.DWG

LEFT\ADV CONT\S3JL081P0253 REV D.DWG

LEFT\ADV CONT\S4JL081P0254 REV E.DWG

LEFT\ADV CONT\S4JL081P0255 REV F.DWG

LEFT\ADV CONT\S4JL081P0256 REV F.DWG



The part I want to extract is anything inclusive from "S3" or "S4" to the
end of the text string but not including the ".dwg" part

Please could someone help me with a function string to do this ?

Thankyou

Try this:

MID(G6,FIND("S3",G6,1),FIND(".DWG",G6,1)-FIND("S3",G6,1))

where G6 is cell with the text.


"someone" wrote in message
...
Hi,

I would like to extract a section of text from a list of drawing numbers.

For example ....

LEFT\ADV CONT\S3JL081P0251 REV D.DWG

LEFT\ADV CONT\S3JL081P0252 REV E.DWG

LEFT\ADV CONT\S3JL081P0253 REV D.DWG

LEFT\ADV CONT\S4JL081P0254 REV E.DWG

LEFT\ADV CONT\S4JL081P0255 REV F.DWG

LEFT\ADV CONT\S4JL081P0256 REV F.DWG



The part I want to extract is anything inclusive from "S3" or "S4" to the
end of the text string but not including the ".dwg" part

Please could someone help me with a function string to do this ?

Thankyou

Since these are of the same length and are set up the same way, you can use
'text to columns' from the Data menu. Choose "fixed width" then click in
the white portion of the "data preview" window to setup where you want the
separation done.



"someone" wrote in message
...
Hi,

I would like to extract a section of text from a list of drawing numbers.

For example ....

LEFT\ADV CONT\S3JL081P0251 REV D.DWG

LEFT\ADV CONT\S3JL081P0252 REV E.DWG

LEFT\ADV CONT\S3JL081P0253 REV D.DWG

LEFT\ADV CONT\S4JL081P0254 REV E.DWG

LEFT\ADV CONT\S4JL081P0255 REV F.DWG

LEFT\ADV CONT\S4JL081P0256 REV F.DWG



The part I want to extract is anything inclusive from "S3" or "S4" to the
end of the text string but not including the ".dwg" part

Please could someone help me with a function string to do this ?

Thankyou

One way:

=MID(LEFT(A1,LEN(A1)-4),FIND("CONT\",A1)+5,255)

Or, if the drawing numbers all have the same form as shown:

=MID(A1,15,18)



In article ,
"someone" wrote:

Hi,

I would like to extract a section of text from a list of drawing numbers.

For example ....

LEFT\ADV CONT\S3JL081P0251 REV D.DWG

LEFT\ADV CONT\S3JL081P0252 REV E.DWG

LEFT\ADV CONT\S3JL081P0253 REV D.DWG

LEFT\ADV CONT\S4JL081P0254 REV E.DWG

LEFT\ADV CONT\S4JL081P0255 REV F.DWG

LEFT\ADV CONT\S4JL081P0256 REV F.DWG



The part I want to extract is anything inclusive from "S3" or "S4" to the
end of the text string but not including the ".dwg" part

Please could someone help me with a function string to do this ?

Thankyou

If all drawing numbers are same length and format as your example you could
use DataText to Columns and Fixed Width.

Add or delete breaklines where you want the text isolated.


Gord Dibben Excel MVP

On Wed, 19 Jan 2005 18:12:19 GMT, "someone" wrote:

Hi,

I would like to extract a section of text from a list of drawing numbers.

For example ....

LEFT\ADV CONT\S3JL081P0251 REV D.DWG

LEFT\ADV CONT\S3JL081P0252 REV E.DWG

LEFT\ADV CONT\S3JL081P0253 REV D.DWG

LEFT\ADV CONT\S4JL081P0254 REV E.DWG

LEFT\ADV CONT\S4JL081P0255 REV F.DWG

LEFT\ADV CONT\S4JL081P0256 REV F.DWG



The part I want to extract is anything inclusive from "S3" or "S4" to the
end of the text string but not including the ".dwg" part

Please could someone help me with a function string to do this ?

Thankyou

someone wrote...
I would like to extract a section of text from a list of drawing
numbers.

For example ....
LEFT\ADV CONT\S3JL081P0251 REV D.DWG
LEFT\ADV CONT\S3JL081P0252 REV E.DWG
LEFT\ADV CONT\S3JL081P0253 REV D.DWG
LEFT\ADV CONT\S4JL081P0254 REV E.DWG
LEFT\ADV CONT\S4JL081P0255 REV F.DWG
LEFT\ADV CONT\S4JL081P0256 REV F.DWG

The part I want to extract is anything inclusive from "S3" or "S4" to
the
end of the text string but not including the ".dwg" part

If all records start with 'LEFT\ADV CONT\', then you already have a few
workable answers. If records could start with other stuff, and if what
you want is equivalent to the base filename of partial pathname, i.e.,
everything after the rightmost '\' but excluding the first period to
the right of it and any subsequent text, then the general approach
would be using an array formula like

=LEFT(MID(x,MAX(IF(MID(x,seq,1)="\",seq))+1,1024),
FIND(".",MID(x,MAX(IF(MID(x,seq,1)="\",seq))+1,102 4))-1)

where x is the address of the cell you're parsing and seq is a defined
name referring to something like

=ROW(INDIRECT("1:1024"))

Thanks for all the replies . . .

Probably didn't make myself very clear on original post.

The preceding text could be anything except "S3" or "S4"

All the strings are of unequal length.

someone wrote...
Probably didn't make myself very clear on original post.

The preceding text could be anything except "S3" or "S4"

All the strings are of unequal length.

Simpler (if longer) then.

=LEFT(MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S3")),1 024),
FIND(".",MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S3") ),1024))-1)

"Harlan Grove" wrote in message
oups.com...
someone wrote...
Probably didn't make myself very clear on original post.

The preceding text could be anything except "S3" or "S4"

All the strings are of unequal length.

Simpler (if longer) then.

=LEFT(MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S3")),1 024),
FIND(".",MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S3") ),1024))-1)


BRILLIANT !

Thank You

:-)

"someone" wrote in message
...

"Harlan Grove" wrote in message
oups.com...
someone wrote...
Probably didn't make myself very clear on original post.

The preceding text could be anything except "S3" or "S4"

All the strings are of unequal length.

Simpler (if longer) then.

=LEFT(MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S3")),1 024),
FIND(".",MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S3") ),1024))-1)


BRILLIANT !

Thank You

:-)

Could I be so bold to ask ........ Could you explain it to me please ?

someone wrote...
....
=LEFT(MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S3") ),1024),
FIND(".",MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S 3")),1024))-1)

Could I be so bold to ask ........ Could you explain it to me please ?

You're looking for only "S3" or "S4". This works because these two
strings are the same length.

The SUBSTITUTE call replaces "S4" with "S3" if there are any instances
of "S4" in x; otherwise, it leaves x as-is.

The SEARCH call then locates the leftmost instance of "S3" in the
string returned by SUBSTITUTE, so the leftmost character position of
"S3" or "S4" in the original x.

The MID call extracts the portion of x starting with the leftmost "S3"
or "S4" to the right end of x. The 1024 as 3rd argument to MID is a
dummy large number. MID won't return more characters than are in its
string 1st argument, so using an arbitrarily large 3rd argument ensures
MID returns the remaining substring to the right of its starting
position 2nd argument.

The FIND call locates the leftmost "." *after* the leftmost "S3" or
"S4".

The LEFT call returns the portion of the MID call's result to the left
of the leftmost "." following (to the right of) the leftmost "S3" or
"S4" in x.

"Harlan Grove" wrote in message
ups.com...
someone wrote...
...
=LEFT(MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S3" )),1024),
FIND(".",MID(x,SEARCH("S3",SUBSTITUTE(x,"S4"," S3")),1024))-1)

Could I be so bold to ask ........ Could you explain it to me please ?

You're looking for only "S3" or "S4". This works because these two
strings are the same length.

The SUBSTITUTE call replaces "S4" with "S3" if there are any instances
of "S4" in x; otherwise, it leaves x as-is.

The SEARCH call then locates the leftmost instance of "S3" in the
string returned by SUBSTITUTE, so the leftmost character position of
"S3" or "S4" in the original x.

The MID call extracts the portion of x starting with the leftmost "S3"
or "S4" to the right end of x. The 1024 as 3rd argument to MID is a
dummy large number. MID won't return more characters than are in its
string 1st argument, so using an arbitrarily large 3rd argument ensures
MID returns the remaining substring to the right of its starting
position 2nd argument.

The FIND call locates the leftmost "." *after* the leftmost "S3" or
"S4".

The LEFT call returns the portion of the MID call's result to the left
of the leftmost "." following (to the right of) the leftmost "S3" or
"S4" in x.

Thank you so much for your help and efforts.

:-)