EXTRACTING TEXT

[Guest]

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

 

[Frank Kabel]

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))

 

[Guest]

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

 

[Guest]

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 Data>Text to Columns
Choose Fixed Width for this case
Place line to seperate your text from the STP and click finish.