7 Replies Latest reply: Nov 20, 2012 10:42 PM by Barry
chronus333 Level 1 Level 1 (0 points)

I have a spreadsheet that contains several columns of text which are all uppercase. I would like to format the text so that it has a capital first letter, and the subsequent letters are lower case for that word. The "Title" case which is found in Format>Font>Capitalization>Title Case only seems to work if the text is all lower case. I can't get it to work on any other types of formatted text. Any thoughts on how I can do this? Thanks


Numbers, OS X Mountain Lion (10.8.2)
  • 1. Re: Title Case iWork Numbers
    Badunit Level 6 Level 6 (10,815 points)

    I don't think any third-party apps will correct this either except one cell at a time.  Here is an idea:

     

    Let's say your text is in column B.

    1. Add a column to the right of it. This will be new column C
    2. In column C use the formula =PROPER(B)
    3. Select all the cells in column C
    4. Paste Values into column B, overwriting the text that is there.
    5. Delete column C
  • 2. Re: Title Case iWork Numbers
    Peggy Level 8 Level 8 (38,230 points)

    I use an AppleScript to change to title case in Pages. This is based on an old AppleWorks script that I've modified for Pages. Copy & paste your table from Numbers to a blank Pages document then open AppleScript Editor (in Utilities) & paste the following code in a new window & save as a script. You can then run it to convert the text & then copy & paste back to Numbers.

     

    Give me a couple of days & I'll see if I can make it work in Numbers.

     

    property lowerOnlyWords : {"a", "an", "the", "and", "of", "for", "from", "in", "on", "before", "after", "between", "behind", "with", "without", "to", "through", "against", "is"}

     

    property upperChars : "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    property lowerChars : "abcdefghijklmnopqrstuvwxyz"

     

    tell application "Pages"

              try

                        set selectionFail to (count each document) < 1 or class of selection is not text

              on error

                        set selectionFail to true

              end try

              if selectionFail then

                        display dialog "You must select some text in a word processing document before running this script."

              else

     

                        set theText to selection

                        set theWords to {} & every word in theText

                        set wordCount to count theWords

                        set isFirstWord to true

                        repeat with wordN from 1 to wordCount

                                  set oldWord to word wordN in theText

                                  set doLowerOnly to oldWord is in lowerOnlyWords and not isFirstWord

                                  if doLowerOnly then

                                            set startLowerN to 1

                                  else

                                            set startLowerN to 2

                                            set oldCharacter to character 1 in oldWord

                                            set alphaN to offset of oldCharacter in lowerChars

                                            if alphaN > 0 then

                                                      set character 1 in word wordN in theText to character alphaN in upperChars

                                            end if

                                  end if

                                  repeat with charN from startLowerN to length of oldWord

                                            set oldCharacter to character charN in oldWord

                                            set alphaN to offset of oldCharacter in upperChars

                                            if alphaN > 0 then

                                                      set character charN in word wordN in theText to character alphaN in lowerChars

                                            end if

                                  end repeat

                                  set isFirstWord to false

                        end repeat

      select theText

              end if

    end tell

     

    Peggy_sig.png

  • 3. Re: Title Case iWork Numbers
    chronus333 Level 1 Level 1 (0 points)

    I would do that, but the spreadsheet is eventually exported to XML format. I don't want extra cells filled with data when I go to export.

  • 4. Re: Title Case iWork Numbers
    chronus333 Level 1 Level 1 (0 points)

    I would try this as well, but I have a spreadsheet that needs corrections daily and all of that copying and pasting would get very tiresome. Especially since I do 5 of these spreadsheets a day with something like 10 colums that need corrections on each one. I wonder why Format>Font>Capitalization>Title Case doesnt work for all upper case letters. I hope Apple releases an update that corrects this.

  • 5. Re: Title Case iWork Numbers
    Wayne Contello Level 6 Level 6 (13,615 points)

    Honestly I would not hold my breath for Apple to fix this.  I think you should look up stream to see if you can address the capitalization problem earier in your process.  If not then you may want to use Peggy's Apple script as a workflow that operates on a file BEFORE you open it.

     

    Just my thoughts.

  • 6. Re: Title Case iWork Numbers
    chronus333 Level 1 Level 1 (0 points)

    I have solved my own question. It is tedious but it works. Copy your column or row of cells and paste them into Microsoft Word 2011. Then highlight all of the cells you need to change and press "Shift+F3" to change the case. After pressing it twice it gives me the desired result. Then you can copy and paste it back into numbers. A pain in the neck but it works. Apple fix this!!! :-)

  • 7. Re: Title Case iWork Numbers
    Barry Level 7 Level 7 (29,180 points)

    "Apple fix this!!! :-)"

     

    I suspect that Apple's view is that there's nothing here to 'fix,' the feature is working as designed.

     

    As I read it, Apple's designers assume that if the user has deliberately chosen to use capitals, then it is the user's intention that capitals are the correct choice, and that choice should override any changes that 'Title' capitalization would make. That assumption is certainly correct in the examples in rows 7, 9 and 10 in the example below, and could also be correct in row 2.

    Picture 18.png

     

    You'll see a similar difference of opinion between Apple and MS in the action of the caps lock key. MS treats this key as a toggle that reverses the action of the shift key when applied to alphabet characters—with caps lock 'released', pressing shift + an alpha character produces the upper case version of that character; with caps lock 'engaged', pressiing shift + the same alpha character produces the lower case version. Apple's caps lock key does what its name implies-it locks the alphabet character keys in upper case, regardless of the position of the shift key.

     

    Example:

     

    with caps lock released:

     

    Robert Smith  — typed on either machine

     

    with caps lock engaged, and using the same keystrokes as used above:

     

    Mac: ROBERT SMITH

     

    Windows: rOBERT sMITH

     

    My personal preference is for Apple's interpretation of what the user wants in both these cases. No doubt that preference is influenced by the 'what I'm used to' factor.

     

    If you do think the manner in which Title capitalization operates is a issue that Apple needs to solve, though, your best path to communicate that to Apple is not through this user-to-user forum. Talk to Apple directly through the Provide Numbers Feedback item in the Numbers menu in Numbers '09. You'll find a similar Provide (application) Feedback in the Application menu of all current Apple applications.

     

    Regards,

    Barry