Extracting a word form a text string

[John Calder]

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

 

[bill kuunders]

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

 

[Rick Rothstein]

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)

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

 

[Gord Dibben]

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

 

[Rick Rothstein]

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

 

[Gord Dibben]

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

 

[Max]

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

 

[Rick Rothstein]

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.

 

[Gord Dibben]

Sorry Rick.

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

And i was being thick about the word TEXT.


Gord