#### 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 "Find>find 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
```
 0
Ben1 (161)
12/30/2005 10:13:02 PM
excel.misc 78881 articles. 5 followers.

18 Replies
566 Views

Similar Articles

[PageSpeed] 53

```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 "Find>find 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
```
 0
petersod (12005)
12/30/2005 10:36:02 PM
```Ben,

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

i.e

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 "Find>find 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.aspx/ms-excel/200512/1
```
 0
DARREN
12/30/2005 10:44:37 PM
```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
>
>
> 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 "Find>find 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.aspx/ms-excel/200512/1
>
```
 0
Ben1 (161)
12/30/2005 11:44:02 PM
```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 "Find>find 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
>
```
 0
Ben1 (161)
12/30/2005 11:52:01 PM
```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 "Find>find 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
```
 0
petersod (12005)
12/31/2005 1:33:41 AM
```Dave,

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 copy>paste special>show 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 "Find>find 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
>
```
 0
Ben1 (161)
12/31/2005 2:25:02 AM
```In e2:

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

And copy down.

Ben wrote:
>
> Dave,
>
>
> 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 copy>paste special>show 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 "Find>find 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
```
 0
petersod (12005)
12/31/2005 12:30:35 PM
```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,
>
>
> 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 copy>paste special>show 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 "Find>find 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
> >
```
 0
Ben1 (161)
12/31/2005 6:45:02 PM
```That would be "GENIUS" :) Thanks again.

"Dave Peterson" wrote:

> In e2:
>
> =if(sum(countif(d2,{"*apts*","*apartments*"}))>0,d2,"")
>
> 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 copy>paste special>show 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 "Find>find 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
>
```
 0
Ben1 (161)
12/31/2005 6:51:02 PM
```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 copy>paste special>show 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 "Find>find 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
```
 0
petersod (12005)
12/31/2005 7:01:48 PM
```Dave,
No, I did it exactly as your e-mail indicated. I actually did a cut and

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 copy>paste special>show 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 "Find>find 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
> > >
```
 0
Ben1 (161)
12/31/2005 7:33:02 PM
```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
>
> 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 copy>paste special>show 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 "Find>find 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
```
 0
petersod (12005)
12/31/2005 7:42:04 PM
```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
> >
> > 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 copy>paste special>show 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 "Find>find 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
>
```
 0
Ben1 (161)
12/31/2005 8:12:02 PM
```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
> >
> > 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 copy>paste special>show 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 "Find>find 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
>
```
 0
Ben1 (161)
12/31/2005 8:30:02 PM
```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
> > >
> > > 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 copy>paste special>show 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 "Find>find 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
```
 0
petersod (12005)
12/31/2005 9:25:48 PM
```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
> > > >
> > > > 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 copy>paste special>show 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 "Find>find 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
>
```
 0
Ben1 (161)
12/31/2005 9:56:02 PM
```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
> > > > >
> > > > > 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 copy>paste special>show 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 "Find>find 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
```
 0
petersod (12005)
12/31/2005 10:19:46 PM
```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
> > > > > >
> > > > > > 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 copy>paste special>show 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 "Find>find 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
>
```
 0
Ben1 (161)
12/31/2005 10:51:02 PM

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8\$0\$27107\$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Copying dbx files from a CDR back into Outlook
My computer was recently hijakced and I had to reinstall everything. I did however save all of my folders in Outlook onto a CD Rom and I do not know how to transfer them back so I can use them. Also I had an animated version of outlook where you could choose loads of diffent stationery and I cannot seem to find that upgrade anaywhere. Can you help me? Tee <tsahai33@hotmail.com> wrote: > My computer was recently hijakced and I had to reinstall > everything. I did however save all of my folders in > Outlook onto a CD Rom and I do not know how to transfer > them back so...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

WLM transfer to another computer
Hi, I finally moved from Windows 7 RTM to Win7 Pro 64. I did it by installing the new OS on a brand new hard drive, then installed my old hard drive in a 2.5" external enclosure. I've been successful in moving most of my files and settings over, but WLM is the exception. Can someone help answer these questions for me: 1. Where are the actual mail files stored? 2. Where is the account login info stored? 3. In Outlook and OE installing on a new computer, even after moving files, prompted for a full redownload off of the POP server. Anyway to avoid this? Is ther...

Copy and close code
Hi, I have a worksheet which uses an indirect formula to search through a series of files/sheets based on an address to return a value from cell AE20. Once the data has been copied for all the previous days I would like a copy/paste values to occur to replace any of the values returned by the formula. In AU10:AU381 there is a 1 or 0 for each row. 1 meaning that a copy/pastevalues can be performed (decided by if the date is < today), and zero if i need to keep the formulas. Is it possible to have a copy/paste values code for only sheets 2-4 which works when the file is...

copy chart from Excel to Powerpoint
I am trying to copy a chart from Excel to Powerpoint, but the button is grayed out and it won't let me copy it. You can convert the chart to a GIF (a picture) with my Excel Objects converter. http://www.geocities.com/excelmarksway "Hoffperson" wrote: > I am trying to copy a chart from Excel to Powerpoint, but the button is > grayed out and it won't let me copy it. ...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...

Perhaps another Money alternative?
After years of their saying "what's Windows?" I not that the following announcement: --- Announcement: GnuCash 2.1.1 Release - 2007-04-28 17:47 GnuCash 2.1.1 released The GnuCash development team proudly announces GnuCash 2.1.1 aka "Bug-begone", the second of several unstable 2.1.x releases of the GnuCash Open Source Accounting Software which will eventually lead to the stable version 2.2.0. With this new release series, GnuCash is available on Microsoft Windows for the first time... --- I haven't checked into this further just yet, but that will certainly be...

Column help
I have a seating chart I am trying to design for our graduation ceremony. Right now I have the report set up using columns. I have a rectange box with the graduates first, middle and last name in the box. I need 16 columns across the page for the seats which I have working. My problems are that I need a bold line going down the middle to divide the 8th and 9th rows. ( Students come in from 2 lines and meet in the middle of each row.) My next problem is that I need to have another column to the left of the 1st column and one to the right of the 16th column that will count the row num...

Column searching problem
I have a worksheet called "net" containing the following: NETWORK AVG MIN MAX STD SAMPLES ABC 17.17 16.26 17.71 0.4469 19 CBS 12.99 11.69 14.56 0.6524 30 NBC 15.39 14.08 16.70 0.7323 38 NBC* 15.12 14.08 15.99 0.4910 31 Fox 10.63 9.07 12.09 0.9374 8 HBO 10.94 9.07 13.22 1.2507 19 Showtime 11.52 6.84 13.23 1.9017 11 HDNet 18.14 14.22 18.85 1.1619 14 HDNet* 18.45 18.13 18.85 0.2847 13 HDNet-Movies 1...

How can I sum only amounts that are in BOLD format within a column
Hi! I need to get a total from a worksheet that has hundreds of amounts in it. However, I only need the total of the amounts that were marked with have BOLD font. Please help me I don't have much experience with EXCEL. Thank you very much! W a n d a try this for column D Sub sumbold() x = Cells(Rows.Count, "d").End(xlUp).Row For Each c In Range(Cells(2, 4), Cells(x, 4)) If c.Font.Bold Then mysum = mysum + c Next MsgBox mysum End Sub -- Don Guillett SalesAid Software donaldb@281.com "Wanda" <Wanda@discussions.microsoft.com> wrote in message news:89A268A6-...