Extract text

[someone]

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

 

[Niek Otten]

=MID(A1,15,18)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Vlad]

Try this:

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

where G6 is cell with the text.

 

[Dave R.]

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.

 

[JE McGimpsey]

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)

 

[Gord Dibben]

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

Add or delete breaklines where you want the text isolated.


Gord Dibben Excel MVP

 

[Harlan Grove]

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,1024))-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"))

 

[someone]

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.

 

[Harlan Grove]

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")),1024),
FIND(".",MID(x,SEARCH("S3",SUBSTITUTE(x,"S4","S3")),1024))-1)

 

[someone]

someone wrote...

Simpler (if longer) then.

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

BRILLIANT !

Thank You

 

[someone]

BRILLIANT !

Thank You

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

 

[Harlan Grove]

someone wrote...
....

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.

 

[someone]

someone wrote...
...

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.