Help Creating A Formula To Cut and Paste Text

[Mary]

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

[Peo Sjoblom]

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" <mballard5@cfl.rr.com> wrote in message
news:1154910394.000382.290090@m79g2000cwm.googlegroups.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
>

[Max]

"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(ISNUMBER(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
---

[Max]

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]

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
> ---

[Max]

"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
---

[Mary]

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
> ---

[Max]

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