Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting a word form a text string
On Oct 21, 8:46*am, John Calder
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 <spaceTEST<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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting a word form a text string
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" wrote in message ... On Oct 21, 8:46 am, John Calder 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 <spaceTEST<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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting a word form a text string
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 EditReplace. Gord Dibben MS Excel MVP On Tue, 20 Oct 2009 12:46:27 -0700, John Calder 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting a word form a text string
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" wrote in message ... 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" wrote in message ... On Oct 21, 8:46 am, John Calder 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 <spaceTEST<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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting a word form a text string
Your first two examples had the word TEST removed using EditReplace.
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" 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. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting a word form a text string
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting a word form a text string
The person I responded to advised using <spaceTEST<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 ... Your first two examples had the word TEST removed using EditReplace. 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" 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. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting a word form a text string
Sorry Rick.
I overlooked the suggestion to use <spTEST<sp And i was being thick about the word TEXT. Gord On Tue, 20 Oct 2009 19:01:11 -0400, "Rick Rothstein" wrote: The person I responded to advised using <spaceTEST<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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting and replacing the first word in a String of text | Excel Worksheet Functions | |||
Extracting h:mm:ss from text string | Excel Worksheet Functions | |||
Extracting text from a string | Excel Worksheet Functions | |||
Extracting a word from a text string | Excel Discussion (Misc queries) | |||
Extracting from a text string | Excel Worksheet Functions |