how to convert capital letters to lower case

Discussion in 'NZ Computing' started by Chris Mayhew, Oct 5, 2003.

  1. Chris Mayhew

    Chris Mayhew Guest

    Hi

    I have a spreadsheet (Open Office) with some text strings in a column that
    are all capitals which I would like to convert to lower case except for the
    first letter in each word.

    There are 17,088 rows of data (text strings) to convert so I was looking
    for some ideas on how to automate this process.

    Any ideas ?

    TIA
    Chris
    Chris Mayhew, Oct 5, 2003
    #1
    1. Advertising

  2. Chris Mayhew

    KS Guest

    "Chris Mayhew" <> wrote in message
    news:Xns940BB4CC6F3ECnoemale@202.20.93.13...
    > Hi
    >
    > I have a spreadsheet (Open Office) with some text strings in a column that
    > are all capitals which I would like to convert to lower case except for

    the
    > first letter in each word.
    >
    > There are 17,088 rows of data (text strings) to convert so I was looking
    > for some ideas on how to automate this process.
    >
    > Any ideas ?
    >
    > TIA
    > Chris


    I do this in MS Office by copying from Excel and pasting the cells into
    Word, and using CHANGE CASE to convert to Capital Case. Then paste back into
    Excel, and use Text to Columns to split the text back into columns.

    Dont know about Open Office though.

    KS
    KS, Oct 5, 2003
    #2
    1. Advertising

  3. Chris Mayhew

    Chris Mayhew Guest

    "KS" <> wrote in
    news:blo9l7$13o$:

    >
    > "Chris Mayhew" <> wrote in message
    > news:Xns940BB4CC6F3ECnoemale@202.20.93.13...
    >> Hi
    >>
    >> I have a spreadsheet (Open Office) with some text strings in a column
    >> that are all capitals which I would like to convert to lower case
    >> except for

    > the
    >> first letter in each word.
    >>
    >> There are 17,088 rows of data (text strings) to convert so I was
    >> looking for some ideas on how to automate this process.
    >>
    >> Any ideas ?
    >>
    >> TIA
    >> Chris

    >
    > I do this in MS Office by copying from Excel and pasting the cells
    > into Word, and using CHANGE CASE to convert to Capital Case. Then
    > paste back into Excel, and use Text to Columns to split the text back
    > into columns.
    >
    > Dont know about Open Office though.
    >
    > KS
    >
    >
    >


    Thanks, I just worked out a similar way using a program called Edit Pad - I
    copied the cells I wanted to convert to Edit Pad (a notepad replacment) -
    converted to "Title" (first capital letter only) - saved as a *.csv file -
    Opened that in Calc - copied the cells and pasted them back into the still
    highlighted cells in the original spreadsheet.
    Chris Mayhew, Oct 5, 2003
    #3
  4. Chris Mayhew

    Peter Guest

    this quote is from Chris Mayhew of Sun, 05 Oct 2003 17:46 :
    > I have a spreadsheet (Open Office) with some text strings in a column that
    > are all capitals which I would like to convert to lower case except for
    > the first letter in each word.


    Hmmm, there doesn't seem to be a capital case function in OpenOffice.
    This little basic macro (below) seems to do the trick. (Watch out for the
    line wrap on a couple of lines.)

    I see from the other post that you sorted this problem already, but I was
    looking for some practice with OpenOffice macros.

    cheers

    Peter


    ----------------------------
    Function ccase(str As String) As String
    dim result as string
    dim Pos1 as long
    dim Pos2 as long

    result = ""
    Pos1 = 1
    Pos2 = 2

    if str <> "" then
    do while Pos2 <> 0
    Pos2 = instr(Pos1, str, " ")
    msgbox cstr(Pos2)
    if Pos2 <> 0 then
    result = result + ucase(mid(str,Pos1,1)) +
    lcase(mid(str,Pos1+1,Pos2-Pos1))
    Pos1=Pos2 + 1
    else
    result = result + ucase(mid(str,Pos1,1)) +
    lcase(mid(str,Pos1+1,len(str)))
    end if
    loop
    end if
    ccase = result
    End Function
    -----------------------------
    Peter, Oct 5, 2003
    #4
  5. Chris Mayhew

    Peter Guest

    this quote is from Peter of Sun, 05 Oct 2003 19:34 :
    > This little basic macro (below) seems to do the trick. (Watch out for the
    > line wrap on a couple of lines.)


    oops, delete the msgbox line from that macro
    It was for checking as I wrote the macro.
    (I told you I was learning.)


    Peter
    Peter, Oct 5, 2003
    #5
  6. Chris Mayhew

    Chris Mayhew Guest

    Peter <> wrote in
    news::

    > this quote is from Peter of Sun, 05 Oct 2003 19:34 :
    >> This little basic macro (below) seems to do the trick. (Watch out
    >> for the line wrap on a couple of lines.)

    >
    > oops, delete the msgbox line from that macro
    > It was for checking as I wrote the macro.
    > (I told you I was learning.)
    >
    >
    > Peter
    >


    Thanks for your efforts. I don't know much about Macro's either so I guess
    I got lucky with the method I used. Editpad doesn't have the 32K limit
    that I believe Notepad has (?) http://www.editpadpro.com/

    I couldn't get your macro to work but I have a list of 17,085 all capital
    words if you want them - 1 word per line - 154KB text file. I found, just
    as a matter of interest that pasteing them to Editpad was quicker than into
    an Open Office doc (no formatting, of course).

    Chris
    Chris Mayhew, Oct 5, 2003
    #6
  7. Chris Mayhew

    Chris Mayhew Guest

    Chris Mayhew <> wrote in
    news:Xns940BDC520ED2Dnoemale@202.20.93.13:


    BTW I have the same number of records containing multiple all capital words
    per line as well.
    Chris Mayhew, Oct 5, 2003
    #7
  8. On Sun, 05 Oct 2003 04:46:22 GMT, Chris Mayhew <>
    proffered, in : news:Xns940BB4CC6F3ECnoemale@202.20.93.13:

    > I have a spreadsheet (Open Office) with some text strings in a
    > column that are all capitals which I would like to convert to
    > lower case except for the first letter in each word.
    >

    For some reason, the "Title" mode (initial capitals, is accessible in
    OOo Writer, but not OOo Calc. Having seen this issue come up in two
    different places today, I may have pull finger and submit a feature
    request for it. I hope that you have found a satisfactory solution to
    your problem.

    --
    "If there's anything more important than my ego around, I want it
    caught and shot now."
    Max Quordlepleen, Oct 5, 2003
    #8
  9. Chris Mayhew

    Gurble Guest

    On Mon, 6 Oct 2003 00:09:14 +1200, Aaron Lawrence
    <> wrote:

    >Thus spake Peter:
    >> Function ccase(str As String) As String
    >> dim result as string
    >> End Function

    >
    >Innit funny seeing vbscript in another company's product. How jolly
    >thoughtful of the OO team ;)



    Get a life.

    Ever heard of BASIC?

    VBScript isn't exactly "revolutionary". Your post implies that anyone
    who incorporates a scripting language loosely resembling BASIC has
    copied microsoft with their pile of dog vomit VBScript.

    No, Microsoft *didn't* invent Basic. Copied it as usual, sure, but had
    nothing to do with it's inception.

    Idiot.
    Gurble, Oct 5, 2003
    #9
  10. Thus spake Peter:
    > Function ccase(str As String) As String
    > dim result as string
    > End Function


    Innit funny seeing vbscript in another company's product. How jolly
    thoughtful of the OO team ;)

    --
    aaronl at consultant dot com
    For every expert, there is an equal and
    opposite expert. - Arthur C. Clarke
    Aaron Lawrence, Oct 5, 2003
    #10
  11. Chris Mayhew

    Peter Guest

    this quote is from Aaron Lawrence of Mon, 06 Oct 2003 01:09 :
    >
    > Innit funny seeing vbscript in another company's product. How jolly
    > thoughtful of the OO team ;)


    oh so jolly and funny !
    Next you'll be claiming that folks like Dennis Ritchie copied the c
    programming language off Microsoft!
    You should read about Microsoft's innovation here ...
    http://www.vcnet.com/bms/departments/innovation.shtml

    Perhaps you should have posted to alt.humor or alt.jokes


    Peter
    Peter, Oct 5, 2003
    #11
  12. Chris Mayhew

    Peter Guest

    this quote is from Chris Mayhew of Sun, 05 Oct 2003 21:39 :
    >
    > I couldn't get your macro to work


    perhaps either
    1. email me your email address, and I'll send you a working example of the
    macro (unmungle the yahoo address above by removing nospam)
    2. email me your spreadsheet (with just a sample of the data to save
    bandwidth), and I'll add the macro to it
    3. post details here of how it didn't work, eg what steps you did, any error
    messages, etc

    HTH

    Peter
    Peter, Oct 5, 2003
    #12
  13. Chris Mayhew

    Chris Mayhew Guest

    Max Quordlepleen <> wrote in
    news::

    > On Sun, 05 Oct 2003 04:46:22 GMT, Chris Mayhew <>
    > proffered, in : news:Xns940BB4CC6F3ECnoemale@202.20.93.13:
    >
    >> I have a spreadsheet (Open Office) with some text strings in a
    >> column that are all capitals which I would like to convert to
    >> lower case except for the first letter in each word.
    >>

    > For some reason, the "Title" mode (initial capitals, is accessible in
    > OOo Writer, but not OOo Calc. Having seen this issue come up in two
    > different places today, I may have pull finger and submit a feature
    > request for it. I hope that you have found a satisfactory solution to
    > your problem.
    >


    Yes, I have found at least 1 solution that I could get to work though I
    didn't realise that Writer had the "Title" mode (very :$) I did find that
    pasting 17,085 lines of text to Writer took longer than posting it to
    EditPad - OK, it did work out to a 346 or so page document in Writer -
    mainly due to the formatting i'd guess.

    As far as submitting a feature request - it never occured to me that I
    could (or should) because I though I was asking for something quite "off
    the wall" so maybe I should look into doing this.
    Chris Mayhew, Oct 6, 2003
    #13
  14. Chris Mayhew

    Chris Mayhew Guest

    Peter <> wrote in
    news::

    > this quote is from Chris Mayhew of Sun, 05 Oct 2003 21:39 :
    >>
    >> I couldn't get your macro to work

    >
    > perhaps either
    > 1. email me your email address, and I'll send you a working example of
    > the macro (unmungle the yahoo address above by removing nospam)
    > 2. email me your spreadsheet (with just a sample of the data to save
    > bandwidth), and I'll add the macro to it
    > 3. post details here of how it didn't work, eg what steps you did, any
    > error messages, etc
    >
    > HTH
    >
    > Peter
    >


    I'll have another go this evening - you never know I might learn something
    my self but my brain has been getting a bit full lately :)
    Thanks
    Chris
    Chris Mayhew, Oct 6, 2003
    #14
  15. On Mon, 06 Oct 2003 14:05:40 +1200, Aaron Lawrence wrote:

    >
    > Crikey! Talk about leaping to assumptions... I guess this is a taste of
    > what people who (purposely or accidentally) sound like they are
    > supporting Microsoft get in response...


    It is what you get when you insist that the work of
    others is the work of Microsoft.

    If Microsoft use C it doesn't mean that everyone else that uses C is
    copying Microsoft.

    Get used to people challenging your assumptions in newsgroups if you get
    too Microsoft centric or Mac centric Linux centric or X centric
    Olson Johnson, Oct 6, 2003
    #15
  16. Thus spake Gurble:
    > Get a life.
    >
    > Ever heard of BASIC?


    Why so hostile, man?

    > VBScript isn't exactly "revolutionary". Your post implies that anyone
    > who incorporates a scripting language loosely resembling BASIC has
    > copied microsoft with their pile of dog vomit VBScript.


    But I thought they had deliberately done exactly that in order to keep
    compatibility and make it easier for people to change?

    I'm also reasonably sure that Microsoft extended the syntax a lot in
    recent years and many of those extensions are copied in OO... y/n?

    > No, Microsoft *didn't* invent Basic. Copied it as usual, sure, but had
    > nothing to do with it's inception.
    >
    > Idiot.


    Crikey! Talk about leaping to assumptions... I guess this is a taste of
    what people who (purposely or accidentally) sound like they are
    supporting Microsoft get in response...

    --
    aaronl at consultant dot com
    For every expert, there is an equal and
    opposite expert. - Arthur C. Clarke
    Aaron Lawrence, Oct 6, 2003
    #16
  17. Chris Mayhew

    Peter Guest

    this quote is from Chris Mayhew of Mon, 06 Oct 2003 12:20 :
    >
    > I'll have another go this evening - you never know I might learn something
    > my self but my brain has been getting a bit full lately :)


    as a completely wild guess - did you click the compile button before
    expecting the function macro to run? (then force a recalc by copying the
    function to another cell, or altering the input cell)

    HTH

    Peter
    Peter, Oct 6, 2003
    #17
  18. On Sun, 05 Oct 2003 23:12:05 GMT, Chris Mayhew <>
    proffered, in : news:Xns940C7C06AC63Fnoemale@202.20.93.13:

    > As far as submitting a feature request - it never occured to me
    > that I could (or should) because I though I was asking for
    > something quite "off the wall" so maybe I should look into doing
    > this.
    >

    I've now done it, so if you're registered with OOo, just look for issue
    20755, and cast your vote. 8^)

    --
    Q: I was listening to a CD in Grip and it sounded horrible! What's up?
    A: Perhaps you are listening to country music...
    (From the Grip "First Asked Questions")
    Max Quordlepleen, Oct 6, 2003
    #18
  19. Chris Mayhew

    Peter Guest

    this quote is from me here of Tue, 07 Oct 2003 17:46 :
    > =proper(a1) Proper Case


    well spotted !
    I had looked in HELP and auto functions for 'title case' and 'capital case',
    but didn't spot they had called it 'proper case'.
    It's one of the issues when migrating between apps, generally the functions
    are all there, but called different names. The trick is to work out the
    other names.


    Peter
    Peter, Oct 6, 2003
    #19
  20. Chris Mayhew

    me here Guest

    Chris Mayhew wrote:
    > Hi
    >
    > I have a spreadsheet (Open Office) with some text strings in a column that
    > are all capitals which I would like to convert to lower case except for the
    > first letter in each word.
    >
    > There are 17,088 rows of data (text strings) to convert so I was looking
    > for some ideas on how to automate this process.
    >
    > Any ideas ?
    >
    > TIA
    > Chris


    1. Search the HELP for "Text Functions"
    2. Use the Function Autopilot (Shitf+F2) and select the TEXT category

    Example string/text functions

    =lower(a1) Lower Case
    =upper(a1) Upper Case
    =proper(a1) Proper Case

    IMPORTANT TO REMEMBER FOR EXCEL USERS: Open Office formulas use a
    semi-colon to delimit functions. Took me all of 5 minutes to become
    accustomed to this. The functions available are quite impressive.
    Eg;
    =VLOOKUP(H598;Payee;2) in this example payee is a named range
    =IF(G598>0;"Earnt";"Spent")


    Gavin
    me here, Oct 7, 2003
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ch. Rajinder Nijjhar Jatt

    Upper and Lower case letters

    Ch. Rajinder Nijjhar Jatt, Nov 2, 2003, in forum: Computer Support
    Replies:
    5
    Views:
    709
    slumpy
    Nov 2, 2003
  2. Ch. Rajinder Nijjhar Jatt

    Upper and lower case

    Ch. Rajinder Nijjhar Jatt, Dec 26, 2004, in forum: Computer Support
    Replies:
    6
    Views:
    531
    Ch. Rajinder Nijjhar Jatt
    Dec 26, 2004
  3. Martin ©¿©¬  @REMOVETHIS.plus.com

    Caps to lower case

    Martin ©¿©¬ @REMOVETHIS.plus.com, Feb 23, 2006, in forum: Computer Support
    Replies:
    4
    Views:
    387
    old man
    Feb 23, 2006
  4. Sigi Rindler

    Excel: swithing lower to upper case possible?

    Sigi Rindler, Nov 25, 2006, in forum: Computer Support
    Replies:
    2
    Views:
    672
    SgtMinor
    Nov 26, 2006
  5. Nervous Nick

    in PS 7.0 how does one force lower-case extensions

    Nervous Nick, Apr 21, 2007, in forum: Digital Photography
    Replies:
    3
    Views:
    413
    Mike Russell
    Apr 21, 2007
Loading...

Share This Page