| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing Text in a cell
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing Text in a cell
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) "FLAgent" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing Text in a cell
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? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "FLAgent" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing Text in a cell
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_Li st) copy down as far as needed "FLAgent" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing Text in a cell
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing a color in a cell based on the text in another cell | Excel Discussion (Misc queries) | |||
| Changing text in a cell from last name first to first name first. | Excel Discussion (Misc queries) | |||
| Copy text from cell to cell with one cell changing text | Excel Worksheet Functions | |||
| Cell colors or text color changing when date in cell gets closer. | Excel Worksheet Functions | |||
| Changing certain text within a cell | Excel Discussion (Misc queries) | |||
Linear Mode
