Excel 'Proper' Formula

[BarTopDancer]

Is anyone familiar with the 'Proper' formula in Excel? I need to apply it to a spreadsheet that goes from A1:E22000 (no joke). I've worked on it and one of our IT guys worked on it and we both end up with the same circular reference. We've also tried inserting new columns, which work, however we can't have the duplicate columns (hiding them is a last resort) The data in the cells is unlikely to change, however there may be new cells added and cells deleted.

Any/all help is welcome. Thanks!

[scaeagles]

I'm pretty well versed in Excel, and I guess I'm confused as to how you can get a circular reference using "proper".

That is one hell of a big spreadsheet.

Can you tell me what you've typed in the specific cell that gives you the circular reference?

[BarTopDancer]

Ya, it's a huge spreadsheet. It was given to us in all CAPS which is harsh on the eyes. We're also confused as to how we're getting a circular reference.

I know I tried:

=proper(A1:E220000)
=proper(A1:A220000)
=proper (A1:E1)

I can't recall which one gave the circular reference (most likely the first), and tracing of the reference doesn't show us the error.

[Kevy Baby]

So, it looks like you are trying to make a one-time fix of the spreadsheet.

Are there any formulas you are trying to preserve or is this just a list of information? If there are no formulas, your fix is quite easy, especially since you only have five columns. In cell F1, type "=PROPER(A1)" with no absolute referencing. Copy that cell, highlight cells A1:E22000 and Paste. With those cells you just pasted still highlighted, choose Copy and then go to the Edit menu and choose "Paste Special..." (a favorite and underused command). In the resulting dialog box choose "Values." That will give a cleaned up database (and BTW: I taught you this command once before!!!).

If there are formulas involved, I will have to put a little more thought into it.

[BarTopDancer]

Thanks Kevin. I'll try this tomorrow. I use the 'paste special' all the time. It comes in handy when copying lists from a web based program into a spreadsheet.

It's 22000 lines of words. Some of the colums have blank cells. Thankfully I didn't have to type this list :eek:.

I'm not sure why we didn't think of what you suggested. Probably because we were too busy trying to undo the circular reference.

[Kevy Baby]

I am often guilty of looking for the complex answer when the easy answer is right in front of me.

[BarTopDancer]

I got it to work but I had to do each column separately. When I did them at the same time I got #REF errors.

Thanks again Kevy!!!!!!

[Kevy Baby]

Here's another one. I used to know how to do this, but have since lost the formula.

Basically, I want to be able to embed a formula in the middle of a text string. For example, if A1 had "3" and A2 had "4", in A3 you could have "I have (A1+A2) apples" with a result of "I have 7 apples." Except there is a lot more to the formula.

I know it can be done, I just can't remember how.