Extracting a word form a text string

Hi

I run Excel 2K

I have a number of text strings with each string containing a key word which 
is located in different postitions of each text string.

I need a formula that extracts only that key word from the text string.

Lets say the word is TEST

Thanks
0
JohnCalder (178)
10/20/2009 7:46:27 PM
excel.newusers 15348 articles. 2 followers. Follow

8 Replies
584 Views

Similar Articles

[PageSpeed] 50

On Oct 21, 8:46=A0am, John Calder <JohnCal...@discussions.microsoft.com>
wrote:
> Hi
>
> I run Excel 2K
>
> I have a number of text strings with each string containing a key word wh=
ich
> is located in different postitions of each text string.
>
> I need a formula that extracts only that key word from the text string.
>
> Lets say the word is TEST
>
> Thanks

I assume you want to keep the rest
One way

Use "edit" "replace...."
In the find you enter <space>TEST<space>
in the replace you enter nothing

If the word happens to be right at the end or beginning of the text
string you have to do it again without the space at the end or start
of the "find what" entry.
Greetings from NZ
0
10/20/2009 8:41:34 PM
That won't necessarily work all the time. Consider text strings like the 
following...

      "Consider this location for TEST, it won't be replaced."

      "This text (with the word TEST) won't work either."

      "Do you think the replacement will happen here for TEXT?"

       and so on...

I think a VB macro, probably using Regular Expressions, will be necessary to 
handle this.

-- 
Rick (MVP - Excel)


"bill kuunders" <bill.kuunders@gmail.com> wrote in message 
news:be2d5423-e111-4e6a-b468-663be2d89988@2g2000prl.googlegroups.com...
On Oct 21, 8:46 am, John Calder <JohnCal...@discussions.microsoft.com>
wrote:
> Hi
>
> I run Excel 2K
>
> I have a number of text strings with each string containing a key word 
> which
> is located in different postitions of each text string.
>
> I need a formula that extracts only that key word from the text string.
>
> Lets say the word is TEST
>
> Thanks

I assume you want to keep the rest
One way

Use "edit" "replace...."
In the find you enter <space>TEST<space>
in the replace you enter nothing

If the word happens to be right at the end or beginning of the text
string you have to do it again without the space at the end or start
of the "find what" entry.
Greetings from NZ 

0
10/20/2009 9:10:26 PM
You want to extract the word TEST to another cell or just replace the word
with nothing?

To extract it, type TEST in an adjacent cell.

To replace it, employ Edit>Replace.


Gord Dibben  MS Excel MVP

On Tue, 20 Oct 2009 12:46:27 -0700, John Calder
<JohnCalder@discussions.microsoft.com> wrote:

>Hi
>
>I run Excel 2K
>
>I have a number of text strings with each string containing a key word which 
>is located in different postitions of each text string.
>
>I need a formula that extracts only that key word from the text string.
>
>Lets say the word is TEST
>
>Thanks

0
Gord
10/20/2009 9:27:37 PM
Maybe a macro like this (where the OP would select all the cells he wanted 
to do the replacement on before running it)...

Sub TextFilter()
  Dim RegEx As Object, Cell As Range
  Set RegEx = CreateObject("VBScript.RegExp")
  RegEx.Global = True
  RegEx.IgnoreCase = True
  RegEx.Pattern = "\bTEST\b"
  For Each Cell In Selection
    Cell.Value = WorksheetFunction.Trim(RegEx.Replace(Cell.Value, ""))
  Next
End Sub

-- 
Rick (MVP - Excel)


"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:OEYprmcUKHA.4000@TK2MSFTNGP05.phx.gbl...
> That won't necessarily work all the time. Consider text strings like the 
> following...
>
>      "Consider this location for TEST, it won't be replaced."
>
>      "This text (with the word TEST) won't work either."
>
>      "Do you think the replacement will happen here for TEXT?"
>
>       and so on...
>
> I think a VB macro, probably using Regular Expressions, will be necessary 
> to handle this.
>
> -- 
> Rick (MVP - Excel)
>
>
> "bill kuunders" <bill.kuunders@gmail.com> wrote in message 
> news:be2d5423-e111-4e6a-b468-663be2d89988@2g2000prl.googlegroups.com...
> On Oct 21, 8:46 am, John Calder <JohnCal...@discussions.microsoft.com>
> wrote:
>> Hi
>>
>> I run Excel 2K
>>
>> I have a number of text strings with each string containing a key word 
>> which
>> is located in different postitions of each text string.
>>
>> I need a formula that extracts only that key word from the text string.
>>
>> Lets say the word is TEST
>>
>> Thanks
>
> I assume you want to keep the rest
> One way
>
> Use "edit" "replace...."
> In the find you enter <space>TEST<space>
> in the replace you enter nothing
>
> If the word happens to be right at the end or beginning of the text
> string you have to do it again without the space at the end or start
> of the "find what" entry.
> Greetings from NZ 

0
10/20/2009 9:48:19 PM
Your first two examples had the word TEST removed using Edit>Replace.

The third example with the word TEXT was not replaced.

What are you getting at?


Gord Dibben  MS Excel MVP

On Tue, 20 Oct 2009 17:10:26 -0400, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>That won't necessarily work all the time. Consider text strings like the 
>following...
>
>      "Consider this location for TEST, it won't be replaced."
>
>      "This text (with the word TEST) won't work either."
>
>      "Do you think the replacement will happen here for TEXT?"
>
>       and so on...
>
>I think a VB macro, probably using Regular Expressions, will be necessary to 
>handle this.

0
Gord
10/20/2009 10:51:08 PM
One way
In B2, copied down: =IF(ISNUMBER(SEARCH("TEST",A2)),"TEST","")
If you need it to be a stricter case sensitive search, replace SEARCH with 
FIND
Any good?, hit the YES below
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
--- 
"John Calder" wrote:
> I run Excel 2K
> 
> I have a number of text strings with each string containing a key word which 
> is located in different postitions of each text string.
> 
> I need a formula that extracts only that key word from the text string.
> 
> Lets say the word is TEST
> 
> Thanks
0
demechanik (4694)
10/20/2009 10:58:02 PM
The person I responded to advised using <space>TEST<space> as the Find 
string, not just TEST by itself in order to get to the word TEST as a 
stand-alone word and not imbedded within other text (such as TESTING, 
INTESTATE, etc... I was just pointing out those surrounding spaces were not 
sufficient to do that. The word TEXT was a mistype of the word TEST.

-- 
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:jhfsd5dcddj1jstocgflatuurqg7gg7pdu@4ax.com...
> Your first two examples had the word TEST removed using Edit>Replace.
>
> The third example with the word TEXT was not replaced.
>
> What are you getting at?
>
>
> Gord Dibben  MS Excel MVP
>
> On Tue, 20 Oct 2009 17:10:26 -0400, "Rick Rothstein"
> <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:
>
>>That won't necessarily work all the time. Consider text strings like the
>>following...
>>
>>      "Consider this location for TEST, it won't be replaced."
>>
>>      "This text (with the word TEST) won't work either."
>>
>>      "Do you think the replacement will happen here for TEXT?"
>>
>>       and so on...
>>
>>I think a VB macro, probably using Regular Expressions, will be necessary 
>>to
>>handle this.
> 

0
10/20/2009 11:01:11 PM
Sorry Rick.

I overlooked the suggestion to use <sp>TEST<sp>

And i was being thick about the word TEXT.


Gord


On Tue, 20 Oct 2009 19:01:11 -0400, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>The person I responded to advised using <space>TEST<space> as the Find 
>string, not just TEST by itself in order to get to the word TEST as a 
>stand-alone word and not imbedded within other text (such as TESTING, 
>INTESTATE, etc... I was just pointing out those surrounding spaces were not 
>sufficient to do that. The word TEXT was a mistype of the word TEST.

0
Gord
10/21/2009 5:04:12 PM
Reply:

Similar Artilces:

Form and Subform for single ID
Hi I am in the process of creating a Case Management database. Every week, a coach meets with the students for Case Management purposes. When the youth becomes involved in Case Management, they complete surveys at 6 months intervals. I also have several tables (forms) which are based on survey questions which must be completed at six months intervals. The main youth form is in the Participant table (there is YouthID is the PK). I have created a One-to-Many relationship between this table and the survey tables but there is no referential integrity since I have more youths...

Font set for reading plain text
I've set Courier as the font to read and compose plain text message. However, I don't have idea why my Outlook showed with Arial rather than Courier. The message was from Yahoo account. It seems no difference where messages came from. Can Outlook show plain text message with the font that I set? Thanks, Charles OL2003 SP2 on XP Pro SP2 ...

How do I use the connection string to connect to a table on the database that I am working on.
I have two related tables. and one form of which one of the tables is a subform and i want to write the data on the subform to a textfile. I used this code to connect to the table but because I have already opened the MDB. I get the error that the file is already openned. objConn.CursorLocation = adUseClient objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Documents and Settings\exds006\Desktop\Electronic LIV;" & "Persist Security Info=False" objConn.Open Is there another way of accessing th...

Text Box Control contains generated hyperlink
Hi. I have a textbox control on a report that contains a generated hyperlink. I output this report to a PDF and when I click the link it only returns the first line of information. This link contains links to documents that reside in a database, so if the link should contain 10 documents, it only returns 8 because that's what fits on the first line of the link. Any ideas of what is happening? Cathy ...

PDFs and Word Documents in Access Report
I'm trying to give the users an option to insert documents or PDFs into the report. I added an oleobject to the report. However, only page one will be displayed. Is there a way around this? I'd like to just store the filename in my table, then load the document into the OLE Object when the report is loaded. Thanks Vayse There is no method available to display more than the first page of the document. You simply cannot combine an Access report with an external document. The only workaround would be to programmatically: Print all/portion of the Access report Print the external...

download Word file into visio 2007
Anyone know how I can import a normal .doc WORD file which has our schools plan into a trial copy of visio 2007. Was going to us Smartdraw but thought as WORD already in use it would be easy to import into another Microsoft programme. Any assistance would be much appreciated. Thanks Alan Speer Since Word is a text tool and Visio is a diagramming tool, what would be the purpose. You might consider just hyperlinking to the word document instead. al "Alan Speer" <Alan Speer@discussions.microsoft.com> wrote in message news:7EA0022B-9B77-4764-AD35-5D342039D2E7@microsoft...

Sheet tab text color
...

Word/Outlook convert content to a table when forwarding msgs
How do I prevent Word/Outlook from converting the content of a forwarded message to a table (actually, a bunch of nested tables) when I forward a message? Outlook/Word doesn't do this. Apparently the original message was already formatted this way. If you are convince that is not the case (look at the HTML source of the received message), then you'll have to provide some configuration details for us to work with and steps to reproduce. Also, does this happen to all messages or just this one? -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outloo...

Is it possible to have text and currancy in one cell and ...
Is it possible to have text and currancy in one cell and be able to use the numbers in that cell to make an equation with another? The short answer .. No -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "thegen" <thegen@discussions.microsoft.com> wrote in message news:1552FF10-9CAA-42AD-B401-B0D229D6A343@microsoft.com... > Is it possible to have text and currancy in one cell and be able to use the > numbers in that cell to make an equation with another? well maybe depending on how the "text & currency" was ar...

How to skip cells with text strings.
What should I add to the code below to cause it to skip a cell tha contains a text string in the cell above it? I need it to only work o numbers in the cell above it. If ActiveCell.Offset(rowoffset:=-1) <> "" Then ActiveCell = ActiveCell.Offset(rowoffset:=-1) + 1 Application.SendKeys ("{Enter}") End If Thanks so much. mikebur -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?thread...

Excel text to Word text
How can I copy Excel text, then paste it into MS Word without carrying over the grid lines? Any help would be appreciated. Thanks Ed English I don't believe you can copy and paste without the gridlines. However, in Word, select the data and Table>Convert>Table to Text. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Ed" <ee15803@att.net> wrote in message news:3f7e21e9.53324199@netnews.worldnet.att.net... > How can I copy Excel text, then paste it into MS Word without carrying > over the grid lines? Any help would be appreciated. Thanks &g...

CCTV text insertion??
Is there anyone using text insertion from RMS? I need a way to insert text most times this is done off receipt printer or off a com port. The reason i dont like using a receipt funtion is rms dumps all the info at the end of the transaction i would rather have it while the cashier is ringing up that item. I would use it as a pole display but not everything is sent to the pole display. JUst wondering if anyone else was using text insertion on a rms system. thanks for the help Nope right now only way is printer. "Doug Pic-N-Pac" <DougPicNPac@discussions.microsoft.com> w...

Convert Incoming Plain Text to HTML
Is there a way to have Outlook (2003) automatically convert incoming Plain Text messages to HTML without manually changing the formatting option? Thanks in advance, Jody As far as I know, no. You can go from HTML to Plain Text but not the other way. "J. Owens" wrote: > Is there a way to have Outlook (2003) automatically convert incoming Plain > Text messages to HTML without manually changing the formatting option? ...

Why does Outlook display text in yellow? How can i change it?
When pasting text into an e-mail, it appears in pale yellow. Sometimes, parts of meesages from others are also yellow. How can I prevent this? Are you using stationery by any chance? -- Kathleen Orland "Wise Dog" <Wise Dog@discussions.microsoft.com> wrote in message news:2D2FBAF7-2B2B-4C66-AE33-B2CA5A51B821@microsoft.com... > When pasting text into an e-mail, it appears in pale yellow. Sometimes, > parts of meesages from others are also yellow. How can I prevent this? ...

windows forms controls
Does a control similar to the the asp.net repeater or datalist exist for winforms? One where I can have a group of controls repeat like in the asp.net repeater. DataGridView doesn't appear to do that. Ralph wrote: > Does a control similar to the the asp.net repeater or datalist exist > for winforms? > One where I can have a group of controls repeat like in the asp.net > repeater. > DataGridView doesn't appear to do that. I don't know much about the ASP.NET controls. But the Windows Forms namespace includes layout controls, such ...

Can't paste text to a document because of macros
When I open a document in Word 07, and try to copy text to it it, I get a message that macros are disabled and to go to help for instructions. Instructions say to close the doc. and reopen with macros enabled. BUT it doesn't say HOW to do that. And, why is this happening all of a sudden and everytime I open a doc.? If this happens with all documents in Word, it's probably being caused by poorly designed code in an add-in (or in the Normal template). Did you install something recently? -- Stefan Blom Microsoft Word MVP "leslie7" <leslie7@discus...

Creating XML Text
Hello, I need to create some well formed XML text live in memory. What I really want is to use the XmlTextWriter object applied to some location in memory (Stream or something?), because it does a nice job of formatting/identing, etc. But I can't figure out how to instantiate the XmlTextWriter object correctly w/o a filespec. I'm not allowed to directly instantiate a Stream & therefore a StreamWriter for that matter since there's no filespec involved. Can someone show me some code to make this work? TIA, -- John C. Bowman Software Engineer Thermo Electron Scientific I...

problem with printing import of word table
Running Vista Ultimate 64-bit; office pro 2007 suite: How do I get rid of the boxes when trying to publishing? I need to import a table created in word 2007. I can get the text box set for margins and size, but I cannot find the command to keep the boxes surrounding each row and column of the table from printing. I apologize in advance for seeming so ignorant, but this is the first time trying this kind of operation. Your help is totally appreciated in advance. Thanks Do you mean the borders? High-light the entire table, Lines, more lines, select the pre-set grid, expand the colo...

Word's return to previous edit function
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The 'previous edit' function no longer works. If I'm in a large file, adding something in the middle of it, then save the file and return to it later, the 'go to your previous edit/cursor insertion point' function doesn't work. <br><br>Help!!! Nothing you can do about it: it's a bug introduced in Word 2008, and they're not going to fix it. When you save, Word cleans up the document, and in doing so, Word 2008 is too aggressive and deletes the bookmarks that "Pre...

counting embedded text #2
If I format the information with "Valid" or "Not Valid" first followe by an explaination, it works. The other option is to separate th status from the explaination. IE: have a separate column called vali and put a yes or no and count those. I only have about 45 entries s far, so it isn't that big of deal. So far, using the wildcard seems to be working -- Shocke ----------------------------------------------------------------------- Shocked's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1489 View this thread: http://www.excelforum.com...

Word 2007
I have created several tables in a Word document. I would like to know if there is a way to copy the table formatting from one table to the next while still mainiting the information that is currently in the table. Well, one way you could do this would be to:- - click in the Table - TableTools - Design contextual tab - Table Styles group - choose a Style in here and then apply the same Style to the other Tables that you have. If my comments have helped please hit Yes. Thanks. "Sammy" wrote: > I have created several tables in a Word d...

Missing text in email
I am having a problem with Users complaining of email missing the text when it was there originally. Also some have gotten emails back from people saying what they sent was blank, then when they check their sent items... sure enough it is blank!! Running Exchange 2003 on Windows 2003 with latest Service Pac Clients are Outlook 2000 with Latest Patches, etc Changing settings as to how the emails are formatted changes nothing (text/rich text/HTML Any help would be...... helpfull What AV are you running? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion ...

Is it possible to convert a Word graphic into a .jpg file?
I want to insert the graphic into a wiki and need it to be in the .jpg format. I am using Word 7. If not possible, any suggestions? Thank you! You probably mean Word 2007. Try selecting the graphic and copying it (CTRL+C). Open Paint and use Paste (CTRL+V) to paste the graphic into Paint. Now you can save the graphic into any format Paint supports. Yves "GECarter" <GECarter@discussions.microsoft.com> wrote in message news:87055F73-61A8-4D77-9B2A-07C800BAD89C@microsoft.com... >I want to insert the graphic into a wiki and need it to be in the .jpg >...

Change amount from number to figure form
I am making appointment letter for employees using mail merge option. In appointment letter I have to put salary in number form i.e. 121000 and also in figures i.e. One lakh twenty one thousand. I have a coloumn in excel where I have all the salary package in number form. Is it possible, for excel to automatically convert this number form salary into figure ? Any assistance would be greatly obliged. Jai Numbers to words Bob Phillips' site for help on this. http://www.xldynamic.com/source/xld.xlFAQ0004.html or Bernd Plumhoff's site http://www.sulprobil.com/html/spellnumber.htm...

Auto Update Text box
Hello, I'm building my first ever Access datebase and I have a form called Enter Comp. In this form I have a combo box called Combo9 that gets it's data from a table called CompType. In the Table CompType I have three fields. The Comp9 combo box gets it data for the second field in the table the field is called Compdescription. On the form Enter Comp I also have a text box called CompAmount. I want this text box to auto populate once a user choices a comptype from the Combo9 combo box with the number that is in the Points table on the CompType table. Currently on the C...