Can I replace contents of a cell without a helper column?

[yabi100]

I have a defined table. I want to amend contents of few cells in a column. This is mainly because operator for input of data didn't use a data validation rule and there are minor mistakes in spelling of contents. For example, cells are like this: Main gate, Main_gate, Main-gate and I have to correct them in order to get proper report in pivot table. Some are much more hard to grasp like: Main gate south, Main gate north. I have to change all of them into Main gate. If there is a fast way to do it, let me know. If there isn't and I have to do it manually (and I think so) can I change without using a helper column?

 

[Automatrix]

In short: Use Power Query, but it will generate a new table, so that's sort of a helper column.

 

[kevin9999]

I have a defined table. I want to amend contents of few cells in a column. This is mainly because operator for input of data didn't use a data validation rule and there are minor mistakes in spelling of contents. For example, cells are like this: Main gate, Main_gate, Main-gate and I have to correct them in order to get proper report in pivot table. Some are much more hard to grasp like: Main gate south, Main gate north. I have to change all of them into Main gate. If there is a fast way to do it, let me know. If there isn't and I have to do it manually (and I think so) can I change without using a helper column?

Do they all start with "Main" followed by something else? If so, you can simply select the column with the values, press Ctrl+H (replace) Find what: Main* Replace with: Main gate and click on Replace All.

 

[yabi100]

I am aware of find and replace (control+H).That is a solution but looking for a better way. perhaps query is a better way.

 

[yabi100]

In short: Use Power Query, but it will generate a new table, so that's sort of a helper column.

Thanks for your reply. New table is OK. Let me tell what I did. I used query to collect data from 4 different files, each file for a year. Then I made these amendments manually in the query table but when I build the pivot table it ignores my manual amendments and brings original filed values. I assume it is not a correct way to amend a query table manually. What I did to overcome this issue, I copied the query table to another sheet, changed it to range. This solved my problem but I know it is not a wise solution. I should amend field values before building the main query table.

 

[Automatrix]

Thanks for your reply. New table is OK. Let me tell what I did. I used query to collect data from 4 different files, each file for a year. Then I made these amendments manually in the query table but when I build the pivot table it ignores my manual amendments and brings original filed values. I assume it is not a correct way to amend a query table manually. What I did to overcome this issue, I copied the query table to another sheet, changed it to range. This solved my problem but I know it is not a wise solution. I should amend field values before building the main query table.

You can probably build a helper column in Power Query that flags cells containing "Main".