Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default copy and pasting a find all list into another column

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
DARREN FONG via OfficeKB.com
 
Posts: n/a
Default copy and pasting a find all list into another column

Ben,

you could try an "IF" formula in the next collumn

i.e

=IF(A1="your text to find",your text to find,"")

then edit/fill/down

and then possibly to remove the formulas

edit/copy

edit/pastespecial/values

Ben wrote:
Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200512/1
  #4   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column

Darren, Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts


I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. Do you have a formula that would look for
either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"DARREN FONG via OfficeKB.com" wrote:

Ben,

you could try an "IF" formula in the next collumn

i.e

=IF(A1="your text to find",your text to find,"")

then edit/fill/down

and then possibly to remove the formulas

edit/copy

edit/pastespecial/values

Ben wrote:
Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200512/1

  #5   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts


I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben


"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks


--

Dave Peterson



  #6   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default copy and pasting a find all list into another column

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts


I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks


--

Dave Peterson


--

Dave Peterson
  #7   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts


I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson

  #8   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default copy and pasting a find all list into another column

In e2:

=if(sum(countif(d2,{"*apts*","*apartments*"}))0,d 2,"")

And copy down.

Ben wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #9   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson

  #10   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column

That would be "GENIUS" :) Thanks again.

"Dave Peterson" wrote:

In e2:

=if(sum(countif(d2,{"*apts*","*apartments*"}))0,d 2,"")

And copy down.

Ben wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson



  #11   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default copy and pasting a find all list into another column

Does that mean you just kept adding more info to that formula, like:

=if(sum(countif(d2,{"*apts*","*apartments*","*llc* ","*ltd*"}))0,d2,"")



Ben wrote:

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #12   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column


Dave,
No, I did it exactly as your e-mail indicated. I actually did a cut and
paste from your thread.

I did not want to complicate the first task to move the data over. The data
in the original column has the suffix to many of word strings like "CO, Corp,
LP, LLC LTD," or others etc after the word Apartment. Some have no suffix and
that is pulled over in the [your] first formula.

I supose I could write a new formula that removes the text that I want to
eliminate or I coulld add it to the same string. All I need is the proper
formula and can add in those words I am looking to strip.

I just thought it would be a less complicated formula to do in two different
steps
and would elimnate any possible error msg. in the original formula.

Thanks once again, you have been a great help. I have been working on this
for days and was too stubborn to go online for help; I went to excell help
and wanted to figure it out myself. So, therefore I am indeed grateful.
Respectfully,
"Ben" wrote:

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson

  #13   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default copy and pasting a find all list into another column

You should be able to just add more strings "*yourwordhere*" (in double quotes
and surrounded by asterisks) to that formula.

That may be enough??

Ben wrote:

Dave,
No, I did it exactly as your e-mail indicated. I actually did a cut and
paste from your thread.

I did not want to complicate the first task to move the data over. The data
in the original column has the suffix to many of word strings like "CO, Corp,
LP, LLC LTD," or others etc after the word Apartment. Some have no suffix and
that is pulled over in the [your] first formula.

I supose I could write a new formula that removes the text that I want to
eliminate or I coulld add it to the same string. All I need is the proper
formula and can add in those words I am looking to strip.

I just thought it would be a less complicated formula to do in two different
steps
and would elimnate any possible error msg. in the original formula.

Thanks once again, you have been a great help. I have been working on this
for days and was too stubborn to go online for help; I went to excell help
and wanted to figure it out myself. So, therefore I am indeed grateful.
Respectfully,
"Ben" wrote:

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #14   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column


Dave, I am looing to find all those with APT or Apartments but stip any word
like llc or ltd or the like before it pulls the cell over. If I add "myword"
to the existing formula w/o some sort of minus or whatever tacked on the end
to check and then strip, won't it bring over the word i was trying to delete
as well?

ex1: cell d2 currentlly is: "The Cedar Woodside Apartments LLC"

I want the result in e2 to be: "The Cedar Woodside Apartments"

ex2: cell d3 "Brooksire Apts LTD Partnership"

Want e2 to be: "Brookshire Apts"

Thanks again.
Ben
"Dave Peterson" wrote:

You should be able to just add more strings "*yourwordhere*" (in double quotes
and surrounded by asterisks) to that formula.

That may be enough??

Ben wrote:

Dave,
No, I did it exactly as your e-mail indicated. I actually did a cut and
paste from your thread.

I did not want to complicate the first task to move the data over. The data
in the original column has the suffix to many of word strings like "CO, Corp,
LP, LLC LTD," or others etc after the word Apartment. Some have no suffix and
that is pulled over in the [your] first formula.

I supose I could write a new formula that removes the text that I want to
eliminate or I coulld add it to the same string. All I need is the proper
formula and can add in those words I am looking to strip.

I just thought it would be a less complicated formula to do in two different
steps
and would elimnate any possible error msg. in the original formula.

Thanks once again, you have been a great help. I have been working on this
for days and was too stubborn to go online for help; I went to excell help
and wanted to figure it out myself. So, therefore I am indeed grateful.
Respectfully,
"Ben" wrote:

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

  #15   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column

Dave, The reason I am so anal about this is because I am using a database
that has different field set up for ownership and then the name of the
property etc

in the past when I did not clean up the data before I imported into the data
base program I several thousand messed up field that I had to replace one by
one in order to do a good and clean mail merge for the biz. I vowed to
rebuild my 2006 database right and clean up all cells and data before the
import.

Sorry for such a detailed explanation but hopelly you understand a little
better my plight to have this as close to perfect as I can once and for all!

Thanks.
Ben

"Dave Peterson" wrote:

You should be able to just add more strings "*yourwordhere*" (in double quotes
and surrounded by asterisks) to that formula.

That may be enough??

Ben wrote:

Dave,
No, I did it exactly as your e-mail indicated. I actually did a cut and
paste from your thread.

I did not want to complicate the first task to move the data over. The data
in the original column has the suffix to many of word strings like "CO, Corp,
LP, LLC LTD," or others etc after the word Apartment. Some have no suffix and
that is pulled over in the [your] first formula.

I supose I could write a new formula that removes the text that I want to
eliminate or I coulld add it to the same string. All I need is the proper
formula and can add in those words I am looking to strip.

I just thought it would be a less complicated formula to do in two different
steps
and would elimnate any possible error msg. in the original formula.

Thanks once again, you have been a great help. I have been working on this
for days and was too stubborn to go online for help; I went to excell help
and wanted to figure it out myself. So, therefore I am indeed grateful.
Respectfully,
"Ben" wrote:

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson



  #16   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default copy and pasting a find all list into another column

I think I'd just use Edit|replace to get rid of those words.

You could build a list in another worksheet, then cycle through that list doing
a bunch of edit|replace's.

I created a new sheet (sheet2) and put
LTD
LLC

In column A of that sheet.

The thing I wanted to do was be able to change: "Apartments LLC" to
"Apartments" and "LTD Something" to "Something", so I figured the code should
look for leading or trailing spaces.

I'd try this against a saved copy of the file--if it does too much damage, just
close without saving:

Option Explicit
Sub testme()

Dim myCell As Range
Dim RngToChange As Range
Dim ValsToFixRng As Range

With Worksheets("Sheet1")
Set RngToChange = .Columns(4)
End With

With Worksheets("sheet2")
Set ValsToFixRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ValsToFixRng.Cells

RngToChange.Replace what:=myCell.Value & " ", _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

RngToChange.Replace what:=" " & myCell.Value, _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

RngToChange.Replace what:=myCell.Value, _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Ben wrote:

Dave, I am looing to find all those with APT or Apartments but stip any word
like llc or ltd or the like before it pulls the cell over. If I add "myword"
to the existing formula w/o some sort of minus or whatever tacked on the end
to check and then strip, won't it bring over the word i was trying to delete
as well?

ex1: cell d2 currentlly is: "The Cedar Woodside Apartments LLC"

I want the result in e2 to be: "The Cedar Woodside Apartments"

ex2: cell d3 "Brooksire Apts LTD Partnership"

Want e2 to be: "Brookshire Apts"

Thanks again.
Ben
"Dave Peterson" wrote:

You should be able to just add more strings "*yourwordhere*" (in double quotes
and surrounded by asterisks) to that formula.

That may be enough??

Ben wrote:

Dave,
No, I did it exactly as your e-mail indicated. I actually did a cut and
paste from your thread.

I did not want to complicate the first task to move the data over. The data
in the original column has the suffix to many of word strings like "CO, Corp,
LP, LLC LTD," or others etc after the word Apartment. Some have no suffix and
that is pulled over in the [your] first formula.

I supose I could write a new formula that removes the text that I want to
eliminate or I coulld add it to the same string. All I need is the proper
formula and can add in those words I am looking to strip.

I just thought it would be a less complicated formula to do in two different
steps
and would elimnate any possible error msg. in the original formula.

Thanks once again, you have been a great help. I have been working on this
for days and was too stubborn to go online for help; I went to excell help
and wanted to figure it out myself. So, therefore I am indeed grateful.
Respectfully,
"Ben" wrote:

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #17   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column

Dave,

Ok, now you're starting to tlk Chineese to me! You are like working on a
doctorite and I am in 1st grade when it comes to these formulas (although I
did get an a in algebra, that was many, many moons ago.) You have been most
gratious with your time and the responses in a short turnaround. I know you
probably have your own work to do and I undersand if you can't spend any more
time with this. I do appreciate what you have done thusfar!

I did not think I would have to use macros to do this. I am familiar with
creating macros in Filemaker but never have done it in excel. I may just have
to do edit replace, however, that means retyping each property name as I go
along and that was what I was trying to avoid. Each property name is unique
unto itself and that is why I need to strip the whole column of several
different trailing words to each name.

If you are able to write an example of what I must do using the "the ceder
woodside apartments LLC" and "Main Steet Apts, Corp" as an example, I would
creat a new column for each word I was looking for one at a time.

Thanks again, and again, and again...

"Dave Peterson" wrote:

I think I'd just use Edit|replace to get rid of those words.

You could build a list in another worksheet, then cycle through that list doing
a bunch of edit|replace's.

I created a new sheet (sheet2) and put
LTD
LLC

In column A of that sheet.

The thing I wanted to do was be able to change: "Apartments LLC" to
"Apartments" and "LTD Something" to "Something", so I figured the code should
look for leading or trailing spaces.

I'd try this against a saved copy of the file--if it does too much damage, just
close without saving:

Option Explicit
Sub testme()

Dim myCell As Range
Dim RngToChange As Range
Dim ValsToFixRng As Range

With Worksheets("Sheet1")
Set RngToChange = .Columns(4)
End With

With Worksheets("sheet2")
Set ValsToFixRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ValsToFixRng.Cells

RngToChange.Replace what:=myCell.Value & " ", _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

RngToChange.Replace what:=" " & myCell.Value, _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

RngToChange.Replace what:=myCell.Value, _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Ben wrote:

Dave, I am looing to find all those with APT or Apartments but stip any word
like llc or ltd or the like before it pulls the cell over. If I add "myword"
to the existing formula w/o some sort of minus or whatever tacked on the end
to check and then strip, won't it bring over the word i was trying to delete
as well?

ex1: cell d2 currentlly is: "The Cedar Woodside Apartments LLC"

I want the result in e2 to be: "The Cedar Woodside Apartments"

ex2: cell d3 "Brooksire Apts LTD Partnership"

Want e2 to be: "Brookshire Apts"

Thanks again.
Ben
"Dave Peterson" wrote:

You should be able to just add more strings "*yourwordhere*" (in double quotes
and surrounded by asterisks) to that formula.

That may be enough??

Ben wrote:

Dave,
No, I did it exactly as your e-mail indicated. I actually did a cut and
paste from your thread.

I did not want to complicate the first task to move the data over. The data
in the original column has the suffix to many of word strings like "CO, Corp,
LP, LLC LTD," or others etc after the word Apartment. Some have no suffix and
that is pulled over in the [your] first formula.

I supose I could write a new formula that removes the text that I want to
eliminate or I coulld add it to the same string. All I need is the proper
formula and can add in those words I am looking to strip.

I just thought it would be a less complicated formula to do in two different
steps
and would elimnate any possible error msg. in the original formula.

Thanks once again, you have been a great help. I have been working on this
for days and was too stubborn to go online for help; I went to excell help
and wanted to figure it out myself. So, therefore I am indeed grateful.
Respectfully,
"Ben" wrote:

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

  #18   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default copy and pasting a find all list into another column

You could use a formula like:

=trim(substitute(d2,"LLC",""))
and
=trim(substitute(d2,"Corp",""))

But you may find that you still have manual effort to do to fix things like that
left over comma:

"Main Steet Apts, Corp"
would become
"Main Steet Apts,"

And
"Main Steet Apts, Corporation"
would become
"Main Steet Apts, oration"

===
Personally, I think I'd go with the Edit|Replace stuff. But remember to save
your data when you're happy with the resulting changes. Then if you change too
much, you can still close without saving and not lose too much.


Ben wrote:

Dave,

Ok, now you're starting to tlk Chineese to me! You are like working on a
doctorite and I am in 1st grade when it comes to these formulas (although I
did get an a in algebra, that was many, many moons ago.) You have been most
gratious with your time and the responses in a short turnaround. I know you
probably have your own work to do and I undersand if you can't spend any more
time with this. I do appreciate what you have done thusfar!

I did not think I would have to use macros to do this. I am familiar with
creating macros in Filemaker but never have done it in excel. I may just have
to do edit replace, however, that means retyping each property name as I go
along and that was what I was trying to avoid. Each property name is unique
unto itself and that is why I need to strip the whole column of several
different trailing words to each name.

If you are able to write an example of what I must do using the "the ceder
woodside apartments LLC" and "Main Steet Apts, Corp" as an example, I would
creat a new column for each word I was looking for one at a time.

Thanks again, and again, and again...

"Dave Peterson" wrote:

I think I'd just use Edit|replace to get rid of those words.

You could build a list in another worksheet, then cycle through that list doing
a bunch of edit|replace's.

I created a new sheet (sheet2) and put
LTD
LLC

In column A of that sheet.

The thing I wanted to do was be able to change: "Apartments LLC" to
"Apartments" and "LTD Something" to "Something", so I figured the code should
look for leading or trailing spaces.

I'd try this against a saved copy of the file--if it does too much damage, just
close without saving:

Option Explicit
Sub testme()

Dim myCell As Range
Dim RngToChange As Range
Dim ValsToFixRng As Range

With Worksheets("Sheet1")
Set RngToChange = .Columns(4)
End With

With Worksheets("sheet2")
Set ValsToFixRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ValsToFixRng.Cells

RngToChange.Replace what:=myCell.Value & " ", _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

RngToChange.Replace what:=" " & myCell.Value, _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

RngToChange.Replace what:=myCell.Value, _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Ben wrote:

Dave, I am looing to find all those with APT or Apartments but stip any word
like llc or ltd or the like before it pulls the cell over. If I add "myword"
to the existing formula w/o some sort of minus or whatever tacked on the end
to check and then strip, won't it bring over the word i was trying to delete
as well?

ex1: cell d2 currentlly is: "The Cedar Woodside Apartments LLC"

I want the result in e2 to be: "The Cedar Woodside Apartments"

ex2: cell d3 "Brooksire Apts LTD Partnership"

Want e2 to be: "Brookshire Apts"

Thanks again.
Ben
"Dave Peterson" wrote:

You should be able to just add more strings "*yourwordhere*" (in double quotes
and surrounded by asterisks) to that formula.

That may be enough??

Ben wrote:

Dave,
No, I did it exactly as your e-mail indicated. I actually did a cut and
paste from your thread.

I did not want to complicate the first task to move the data over. The data
in the original column has the suffix to many of word strings like "CO, Corp,
LP, LLC LTD," or others etc after the word Apartment. Some have no suffix and
that is pulled over in the [your] first formula.

I supose I could write a new formula that removes the text that I want to
eliminate or I coulld add it to the same string. All I need is the proper
formula and can add in those words I am looking to strip.

I just thought it would be a less complicated formula to do in two different
steps
and would elimnate any possible error msg. in the original formula.

Thanks once again, you have been a great help. I have been working on this
for days and was too stubborn to go online for help; I went to excell help
and wanted to figure it out myself. So, therefore I am indeed grateful.
Respectfully,
"Ben" wrote:

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #19   Report Post  
Posted to microsoft.public.excel.misc
Ben
 
Posts: n/a
Default copy and pasting a find all list into another column

THANKSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS! :)

"Dave Peterson" wrote:

You could use a formula like:

=trim(substitute(d2,"LLC",""))
and
=trim(substitute(d2,"Corp",""))

But you may find that you still have manual effort to do to fix things like that
left over comma:

"Main Steet Apts, Corp"
would become
"Main Steet Apts,"

And
"Main Steet Apts, Corporation"
would become
"Main Steet Apts, oration"

===
Personally, I think I'd go with the Edit|Replace stuff. But remember to save
your data when you're happy with the resulting changes. Then if you change too
much, you can still close without saving and not lose too much.


Ben wrote:

Dave,

Ok, now you're starting to tlk Chineese to me! You are like working on a
doctorite and I am in 1st grade when it comes to these formulas (although I
did get an a in algebra, that was many, many moons ago.) You have been most
gratious with your time and the responses in a short turnaround. I know you
probably have your own work to do and I undersand if you can't spend any more
time with this. I do appreciate what you have done thusfar!

I did not think I would have to use macros to do this. I am familiar with
creating macros in Filemaker but never have done it in excel. I may just have
to do edit replace, however, that means retyping each property name as I go
along and that was what I was trying to avoid. Each property name is unique
unto itself and that is why I need to strip the whole column of several
different trailing words to each name.

If you are able to write an example of what I must do using the "the ceder
woodside apartments LLC" and "Main Steet Apts, Corp" as an example, I would
creat a new column for each word I was looking for one at a time.

Thanks again, and again, and again...

"Dave Peterson" wrote:

I think I'd just use Edit|replace to get rid of those words.

You could build a list in another worksheet, then cycle through that list doing
a bunch of edit|replace's.

I created a new sheet (sheet2) and put
LTD
LLC

In column A of that sheet.

The thing I wanted to do was be able to change: "Apartments LLC" to
"Apartments" and "LTD Something" to "Something", so I figured the code should
look for leading or trailing spaces.

I'd try this against a saved copy of the file--if it does too much damage, just
close without saving:

Option Explicit
Sub testme()

Dim myCell As Range
Dim RngToChange As Range
Dim ValsToFixRng As Range

With Worksheets("Sheet1")
Set RngToChange = .Columns(4)
End With

With Worksheets("sheet2")
Set ValsToFixRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ValsToFixRng.Cells

RngToChange.Replace what:=myCell.Value & " ", _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

RngToChange.Replace what:=" " & myCell.Value, _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

RngToChange.Replace what:=myCell.Value, _
replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Ben wrote:

Dave, I am looing to find all those with APT or Apartments but stip any word
like llc or ltd or the like before it pulls the cell over. If I add "myword"
to the existing formula w/o some sort of minus or whatever tacked on the end
to check and then strip, won't it bring over the word i was trying to delete
as well?

ex1: cell d2 currentlly is: "The Cedar Woodside Apartments LLC"

I want the result in e2 to be: "The Cedar Woodside Apartments"

ex2: cell d3 "Brooksire Apts LTD Partnership"

Want e2 to be: "Brookshire Apts"

Thanks again.
Ben
"Dave Peterson" wrote:

You should be able to just add more strings "*yourwordhere*" (in double quotes
and surrounded by asterisks) to that formula.

That may be enough??

Ben wrote:

Dave,
No, I did it exactly as your e-mail indicated. I actually did a cut and
paste from your thread.

I did not want to complicate the first task to move the data over. The data
in the original column has the suffix to many of word strings like "CO, Corp,
LP, LLC LTD," or others etc after the word Apartment. Some have no suffix and
that is pulled over in the [your] first formula.

I supose I could write a new formula that removes the text that I want to
eliminate or I coulld add it to the same string. All I need is the proper
formula and can add in those words I am looking to strip.

I just thought it would be a less complicated formula to do in two different
steps
and would elimnate any possible error msg. in the original formula.

Thanks once again, you have been a great help. I have been working on this
for days and was too stubborn to go online for help; I went to excell help
and wanted to figure it out myself. So, therefore I am indeed grateful.
Respectfully,
"Ben" wrote:

Dave, YOU ARE A GENIOUS! IT WORKS!!!!!!!!

Now for one last request if you would be so kind to assist me.

Some of the word stings in the same cells I am trying to move over have
things like LLC or Ltd or Co or Limited or Partnership etc. tacked on to the
end. How might I make a new or several new formulas for each word I am
looking for to strip. (of course I would have to make a new column for each
until they are all stripped,
Unless I make one other IF formula looking for seveal word to strip.)

Thanks for your help again and I wish you and yours a happy and prosperous
New Year in 2006!
Regards,

"Ben" wrote:

Dave,

Thanks for your help!

I am pulling my hair out here (and I don't have much left l.o.l.); what am I
missing?

What I am trying to do is if a formula finds the word "Apts" or "Apartments"
(non-case sensative) as part of a string of words in a cell, I want it to
paste the whole string of words to a new cell on the same row in a new column.

Ex. If it finds "Cedar Woods Apartments" in d2 I want it to copy "Cedar
Woods Apartments" into e2. If it goes to d3 and the word is not in the cell,
I want it to return a blank cell in e3 and so forth and so on down the e3
column. Once I have the correct formula I know I can drag the formula down to
the last row and it will create a formula for the corresponding row. I also
know how to copypaste specialshow values to remove the formula(s).

So you understand what I am doing, I am a Commercial Real Estate broker and
am trying to extract the ownership vs. property name(s) of a property from a
20000+ row list of properties. Some ownership names on title include the word
APT or Apartment in it and generally it is also the same name as the
building. So I am trying to create a new column called "Property Name"
(starting at G2) from the "Owner on Title" column (starting at D2).

So, therefore I am not trying to get a true or false or "got it" answer,
rather what is actually , totally in the cell & to be pasted to the cell in
the same row so I keep the right property name with the correct address, etc.

I am not just looking to find, but to do a find and then paste. I hope you
can help and your time is greatly appreaciated. Thanks!

Regards,

Ben

"Dave Peterson" wrote:

If you were looking for APTS in anywhere in a cell, you could use:

=if(countif(a1,"*APTS*")1,"APTS","")
And drag down.

But since you're looking for a few things:
=if(sum(countif(a1,{"*apts*","*apartments*"}))0," Found it","")

I'd guess that when you changed the formula to look at a single cell, something
went wrong.

Ben wrote:

Dave,

Thanks for the help however this formula is not working. I am looking
in one column 20000 + rows long for a part of a few words, example:

Woodman Park Apartments or Woodman Park Apts

I am looking for the word Apartments or Apts. That word(s) will not be the
only word in any of the cells. If it is not true, I want it to leave the
cell alone. If true, to paste it to a new cell (same row) one column over. Do
you have a formula that would look for either word?

Thanks. I am a relativly new to formulas.
Regards, Ben

"Dave Peterson" wrote:

Maybe you could use a formula instead of the edit|Find.

Say your data is in Columns A:E and you were looking for Ben in any cell in that
range, you could use a formula like this:

=if(countif(a1:e1,"Ben")0,"Ben","")
and drag down

This formula will count the cell only if Ben is the only thing in that cell. If
Ben is in that cell with other stuff like "Benny and June", you could use a
different formula:

=if(countif(a1:E1,"*ben*")0,"Ben","")
and drag down



Ben wrote:

Does anyone know how I can paste the find all into a new column in the same
corasponding row number? I did a copy from the "Findfind all" list by right
clicking the list then do "copy". Those cells (non-adjacent cells) get
highlighted but when I past it in the next column to the same row it pastes
the list in order not in the correct cell. Must I use formulas to do this and
if so which formula? HELPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!

thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to open workbook and copy and paste values in to orig workbo Dena X Excel Worksheet Functions 1 December 16th 05 12:13 AM
Copy Rows found using Find All feature Scott H Excel Discussion (Misc queries) 3 May 2nd 05 06:04 PM
Howdo U copy a formula down a column, that uses data in another w. Need Help pasting a formula Excel Worksheet Functions 1 February 25th 05 07:04 PM
Pasting all but Column widths doesnt get pasted. Hari Prasadh Excel Discussion (Misc queries) 1 February 13th 05 07:51 PM
Copy and pasting graphs to PowerPoint JZip Excel Discussion (Misc queries) 0 January 6th 05 09:29 PM


All times are GMT +1. The time now is 11:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"