Forum Discussion
PROPER case issue converting existing large amount of rows without copying text into a new column
Hi,
So I'm new to this forum. I'm not an intermediate level of Excel person but I'm just learning about the PROPER formula my problem is I'm applying to about 12,000 rows of data in the same column it would be much easier if I could do a PROPER (x1:x12000) and apply it to existing text that I didn't type in. When you copy the values to a new column and then delete the original column after you do the PROPER change it then comes up with REF# errors. Is there a way to get around this?
Thanks.
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.
6 Replies
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.
