Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import comma delimited text | Excel Discussion (Misc queries) | |||
How do I rotate a text box | Charts and Charting in Excel | |||
Hide text that doesn't fit in the cell | Excel Discussion (Misc queries) | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) | |||
Text Boxes | Excel Discussion (Misc queries) |