Forum Discussion
PROPER case issue converting existing large amount of rows without copying text into a new column
nashpromedcomau You should NOT delete column A as it will break the formulas in column B that refer to cells in column A. That's the way it is. The #REF error will occur. Not sure what "official MS instruction" you have read.
What you need to do is copy column B (the one with the PROPER formulas in it) and paste it as values on top of column A and then delete column B with the formulas as it is no longer needed.
nashpromedcomau The key is to copy the column with the PROPER formulae and paste it somewhere as values. It will remove the formula and just keep the end result. Now you can delete the original column without getting the #REF error.
- The problem is that doesn't work you setup a blank column alongside the data you can only put the PROPER formula in one cell. You can copy the formulas manually or as a paste special but once you move it across it is still referring to the previous column. That's what seems to trigger the REF# error when you remove the column it doesn't like it
nashpromedcomau I suspect that your Excel version doesn't support dynamic arrays, otherwise you should be able to enter =PROPER(X1:X12000) in e.g. Y1 and spill the result to the entire range Y1:Y12000 in one go.
If you can't, then you have no other option than to enter =PROPER(X1) in Y1, double-click on the small square in the bottom right-hand corner of the cell to fill the formula all the way down to the row where column Y ends. That doesn't take much time.
You say "once you move it across it is still referring to the previous column". what exactly are you trying to achieve? If you want to copy the formula across but still have it refer to column X, make the reference partly absolute, i.e. =PROPER($X1:$X12000) or =PROPER($X1) by putting the $ sign before the column references.
If all of this makes no sense, perhaps you can upload a file that demonstrates the problem.
- Sorry I cannot upload the file because it's confidential material what I'm trying to do is
In Column A I have been given Surnames that are in a mixture of UPPER, PROPER (Sentence Case) I'm trying to regularize the data so it's sortable because otherwise when it's not all the same the sort doesn't push information in the correct order. I create a new blank column in this example let's say Column B I then use the formula PROPER=A2 (which refers to the original column data) and I manually pull the formulas down from the first row to the last row in the 12,000 range it does that conversion but if I then delete column A Column B pulls REF errors. I did see the SPILL formula feedback but I'm not familiar with what that does and I tried to create a PROPER(A2:A12000) and it doesn't work. The end result I want is to be able to delete the cr**py data in Column A so I can use Column B after it completes the conversion. The Microsoft official instruction says you can do this but as I said it triggers the REF.
I also then need to fix this in Column that has the First Names of these people in the list because I have the same issue which means I added another column alongside the firstnames in the list.
I then thought I would hide the old (bad) data so it's not visible so I stop focussing on it but I keep wanting to fix it wasting my time.
