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.
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.
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.
- Right that now makes sense I couldn't work out why the REF info wasn't working and I still had the 2 columns. The MS instruction was from the help menu which now makes sense I was copying the formula to column 2 and not recopying the data back into the original column so I thought I had to delete the original column not the blank column.