Where do I enter the PROPER formula in a spreadsheet?

C

Colleen Little

I have a spreadsheet with data containing UPPERCASE. I need to use the
PROPER formual and do not know where to enter this formula on the spreadsheet.
 
B

bpeltzer

You would enter the formula where you want the results to appear. If, for
instance, you want to change all of column A from uppercase to proper, insert
a column before column B. In B1 enter =proper(A1). Copy that formula down
through column A. Then copy/paste special values. Finally, delete column A
if you don't want to keep it around.
 
C

Colleen Little

So I have a large spreadsheet I need to do this on. I would insert a new
column for each column in this spreadsheet. Column A I need to take Cells 3
thru 270 and change to PROPER. What would the formula be for that
=PROPER(A3:A270)? This formula would get inserted into the newly created
column between A and B?
 
B

bpeltzer

I wouldn't use more than one cell as input to the proper function. In B3,
enter =proper(A3) and copy that formula from B3 into B4:B270.
If you have a bunch of columns on which to do this (hopefully less than
129), I'd go to the first empty column and enter the function there,
=proper(a3). Copy down to the last row of data, and across for as many
columns of data as you've got. Then copy/paste special values all the
columns at once.
 
C

Colleen Little

Thank you! This works great.

bpeltzer said:
You would enter the formula where you want the results to appear. If, for
instance, you want to change all of column A from uppercase to proper, insert
a column before column B. In B1 enter =proper(A1). Copy that formula down
through column A. Then copy/paste special values. Finally, delete column A
if you don't want to keep it around.
Click to expand...
 
C

Colleen Little

Yes, that is exactly what I did. Now my issue is I need to import this data
into QuickBooks which I do have knowlege in but when I try to delete the
"old" rows in excel which houses the original data it wipes out the new row.
When I copy past the new rows into a new sheet I get the "XREF!" error and
cannot find documentation on how to correct that.
 
B

bpeltzer

Be sure to lock in the values before deleting the old data. Highlight the
transformed data all at once, then Copy (ctrl + c) and Edit > Paste Special,
select the Values radio button and click OK. (You could paste over the
transformed data, or you could follow the same steps to paste into a new
worksheet).
 
Top