EXTRACTING TEXT
 

I want to extract some text from a cell. The text can appear anwhere within
the string such as "OCXO STP 2337A VAR 1 10 MHZ". The text always beings
with "STP" but can appear as follows:

STP 1234
STP 1234A
STP-1234
STP-1234A
STP1234
STP1234A

I have tried the formula =MID(A152,FIND("STP",A152),8) but the MID is
looking for 8 characters and the number of characters are variable.

Thank you in advance.

Esther

Hi
try the following formulas:
A1: Source string

B1: Start position:
=FIND("STP",A1)

C1: End position:
=FIND(" ",A1,B1+5)

D1: Result:
=MID(A1,B1,C1-B1)

of course you could also combine this to one formula:
=MID(A1,FIND("STP",A1),FIND(" ",A1,FIND("STP",A1)+5)-FIND("STP",A1))

--
Regards
Frank Kabel
Frankfurt, Germany
"EstherJ" schrieb im Newsbeitrag
...
I want to extract some text from a cell. The text can appear anwhere
within
the string such as "OCXO STP 2337A VAR 1 10 MHZ". The text always beings
with "STP" but can appear as follows:

STP 1234
STP 1234A
STP-1234
STP-1234A
STP1234
STP1234A

I have tried the formula =MID(A152,FIND("STP",A152),8) but the MID is
looking for 8 characters and the number of characters are variable.

Thank you in advance.

Esther

If the string after always is "VAR" you could use this

=TRIM(MID(A1,FIND("STP",A1),FIND("VAR",A1)-FIND("STP",A1)))

Regards,

Peo Sjoblom

"EstherJ" wrote:

I want to extract some text from a cell. The text can appear anwhere within
the string such as "OCXO STP 2337A VAR 1 10 MHZ". The text always beings
with "STP" but can appear as follows:

STP 1234
STP 1234A
STP-1234
STP-1234A
STP1234
STP1234A

I have tried the formula =MID(A152,FIND("STP",A152),8) but the MID is
looking for 8 characters and the number of characters are variable.

Thank you in advance.

Esther

Couldn't you perhaps use the "Text to Columns" feature?

Highlight the row you want to break apart. (Make sure you have empty rows
to the right by the number you will be breaking apart)
Click DataText to Columns
Choose Fixed Width for this case
Place line to seperate your text from the STP and click finish.



"EstherJ" wrote:

I want to extract some text from a cell. The text can appear anwhere within
the string such as "OCXO STP 2337A VAR 1 10 MHZ". The text always beings
with "STP" but can appear as follows:

STP 1234
STP 1234A
STP-1234
STP-1234A
STP1234
STP1234A

I have tried the formula =MID(A152,FIND("STP",A152),8) but the MID is
looking for 8 characters and the number of characters are variable.

Thank you in advance.

Esther