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

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,

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

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,

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

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

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

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

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