Changing Text in a cell

[FLAgent]

I'm trying to create a pivot table that summarizes different community sales.

The worksheet has over 5,000 records, but the column that contains the
community names, not all of the names have been entered identically.

The good news is they all at least have the base name. Is there a way I can
change the text in each cell so that all the community names are the same?

Below is a sample of the column with the community names.

So for example, below I want to change all of the cells that "contain"
"Arbor Greene" to just have "Arbor Greene" in the cell. The same with the
cells that "contain" "Easton Park", I want them all to just have "Easton
Park" in the cell, the cells that have "Heritage Isles", I want them all just
to contain "Hertiage Isles", and so on...

This way when I go to create the pivot table it will summarize all the data
by community.

Hopefully that made sense?

Thanks in advance for your help!!

Sample Column

ARBOR GREENE
ARBOR GREENE
ARBOR GREENE
ARBOR GREENE PHASE 5 UNITS 1 A
ARBOR GREENE PHASE 7 UNIT 1
ARBOR GREENE TOWNHOMES REPLAT
EASTON PARK
EASTON PARK PHASE 1
EASTON PARK PHASE 1
HERITAGE ISLES
HERITAGE ISLES
HERITAGE ISLES
HERITAGE ISLES
Heritage Isles Ph 1e
Heritage Isles Ph 1e
Heritage Isles Ph 1e
HERITAGE ISLES PHASE
HERITAGE ISLES PHASE 1D
HERITAGE ISLES PHASE 1E UNIT 1
HERITAGE ISLES PHASE 1E UNIT 2
HERITAGE ISLES PHASE 1E UNIT 2
HERITAGE ISLES PHASE 1E UNIT 2
HERITAGE ISLES PHASE 1E UNIT 2
HERITAGE ISLES PHASE 2E
HERITAGE ISLES PHASE 3A
HERITAGE ISLES PHASE 3A
HERITAGE ISLES PHASE 3A
HERITAGE ISLES PHASE 3A
HERITAGE ISLES PHASE 3E UNIT 1
HERITAGE ISLES PHASE 3E UNIT 2
HERITAGE ISLES,NASSAU POINTE
HUNTERS GREEN
HUNTER'S GREEN
HUNTER'S GREEN
HUNTER'S GREEN
HUNTER'S GREEN
HUNTER'S GREEN
HUNTER'S GREEN PARCEL 18A PHAS
HUNTER'S GREEN PARCEL 20
HUNTER'S GREEN PARCEL 22A PHAS

 

[Joel]

I think you need a new column that contains the base name. Is the bae name
always two words? You can extract everything before the 1st space using this
formula

=Left(A1,Text(" ",A1)-1)

Which will return all the character before the 1st space.

The 2nd space would be

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

 

[Shane Devenshire]

Hi,

It might help if we know what else was in the cell, for example is Arbor
Greene in the first or last position. Is Arbor Greene the only thing what
will contain capital letters, is Arbor Greene preceeded or followed by some
destinguishing characters such as -? Are there a relatively small number of
items that you want to break out?

 

[Teethless mama]

Assuming your data in Column A

Criteria_List
ARBOR GREENE
EASTON PARK
HERITAGE ISLES
HUNTER'S GREEN

=LOOKUP(10^10,SEARCH(Criteria_List,A1),Criteria_List)
copy down as far as needed

 

[FLAgent]

Joel - This almost works. The names are almost all two words, the problem is
when there are only two words in the cell it drops the second word because
there is no space after the second word.

Teethless mama, the data can be in column A. Where does the Criteria_List
go? Or how do I set-up the Criteria_List? Thanks.