Hi everyone. Thanks in advance for any help you may have.

I need to create a formula that searches for a specific word in a
column then cut the word and paste it to another column. Of course
this would be simple if it was the only word in the column, but there
is other text that I would like to remain in the orginal column..


Thanks again,


Mary

Formulas cannot do this, they can only return a value in the cell that
contain the formula.
You would need VBA (macro) for this.

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com


"Mary" wrote in message
oups.com...
Hi everyone. Thanks in advance for any help you may have.

I need to create a formula that searches for a specific word in a
column then cut the word and paste it to another column. Of course
this would be simple if it was the only word in the column, but there
is other text that I would like to remain in the orginal column..


Thanks again,


Mary

"Mary" wrote:
I need to create a formula that searches for a specific word in a
column then cut the word and paste it to another column. Of course
this would be simple if it was the only word in the column, but there
is other text that I would like to remain in the orginal column..

One try at handling the fuzzy here might be along these lines ..

Assuming the col of words is col A, data running in A2 down, and the
specific words that you want to search col A for are listed in B1 across

Put in B2:
=IF(TRIM(B$1)="","",IF(AND(LEN(TRIM($A2))=LEN(TRIM (B$1)),ISNUMBER(SEARCH(TRIM(B$1),$A2))),B$1,IF(ISN UMBER(SEARCH(TRIM(B$1)&" ",$A2)),B$1,"")))
Copy B2 across and fill down to populate

Replace SEARCH with FIND if you need it to be case sensitive. SEARCH is not
case sensitive.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

The earlier suggestion simply populates the table with the specific/key words
listed in B1 across where the search for these words within col A evaluates
to TRUE.

Conversely, if what you want is to strip the keywords listed in B1 across
from the original col A wherever it is found, ie have the stripped version of
col A populated within the table, then think we could try this instead in B2:
=IF(TRIM(B$1)="","",IF(AND(LEN(TRIM($A2))=LEN(TRIM (B$1)),ISNUMBER(SEARCH(TRIM(B$1),$A2))),SUBSTITUTE ($A2,B$1,""),IF(ISNUMBER(SEARCH(TRIM(B$1)&"
",$A2)),TRIM(SUBSTITUTE($A2,B$1,"")),$A2)))
Copy B2 across and fill down to populate, as before
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks, but I need a little more help. Example A2 = Smith Trustee
Should be A2 Smith & B2 Trustee This is just an example. Where do I
need to fill in the word trustee in the formula below? Thanks Mary


Max wrote:
The earlier suggestion simply populates the table with the specific/key words
listed in B1 across where the search for these words within col A evaluates
to TRUE.

Conversely, if what you want is to strip the keywords listed in B1 across
from the original col A wherever it is found, ie have the stripped version of
col A populated within the table, then think we could try this instead in B2:
=IF(TRIM(B$1)="","",IF(AND(LEN(TRIM($A2))=LEN(TRIM (B$1)),ISNUMBER(SEARCH(TRIM(B$1),$A2))),SUBSTITUTE ($A2,B$1,""),IF(ISNUMBER(SEARCH(TRIM(B$1)&"
",$A2)),TRIM(SUBSTITUTE($A2,B$1,"")),$A2)))
Copy B2 across and fill down to populate, as before
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Mary" wrote:
Thanks, but I need a little more help. Example A2 = Smith Trustee
Should be A2 Smith & B2 Trustee This is just an example. Where do I
need to fill in the word trustee in the formula below? Thanks Mary

Ahh, pl disregard the earlier responses which were way-off in the interp <g

Maybe just try splicing col A using Data Text to Columns

Try this on a spare copy:
Assuming empty cols to the right of col A
Select col A, then click Data Text to Columns Delimited Next
In step 2, check "Space" Finish
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Great answer!!! Thanks I new there had to be an easy way to do this.
And all the data I need happens to be at the end of my text. Perfect
thanks. I actually did that earlier with some other data not sure why
it didn't dawn on me to do it again. I guess just not thinking simple
enough.

Thanks, Mary


Max wrote:
"Mary" wrote:
Thanks, but I need a little more help. Example A2 = Smith Trustee
Should be A2 Smith & B2 Trustee This is just an example. Where do I
need to fill in the word trustee in the formula below? Thanks Mary

Ahh, pl disregard the earlier responses which were way-off in the interp <g

Maybe just try splicing col A using Data Text to Columns

Try this on a spare copy:
Assuming empty cols to the right of col A
Select col A, then click Data Text to Columns Delimited Next
In step 2, check "Space" Finish
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad that did it !
Thanks for calling back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mary" wrote:
Great answer!!! Thanks I new there had to be an easy way to do this.
And all the data I need happens to be at the end of my text. Perfect
thanks. I actually did that earlier with some other data not sure why
it didn't dawn on me to do it again. I guess just not thinking simple
enough.

Thanks, Mary