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

 

[Guest]

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.

---

 

[Guest]

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

---

 

[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

 

[Guest]

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

---

 

[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

 

[Guest]

Glad that did it !
Thanks for calling back ..