Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing Text from all CAPS

    I have a database file in MYOB in which the majority of information is in CAPS - ie. NAME AND ADDRESS are all caps!

    Is there a way of importing into Excel or Access and removing caps and replacing with lower case.

    Of course just to make it easy I also require first letter to be a Capital.

    I have a sign making graphics program that can do it but can't get the data in there.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Text from all CAPS

    In an empty column, in the same row as the first row with the all caps, enter the formula:

    <pre>=Proper(A1)
    </pre>


    Where A1 is replaced with the cell where the all caps data is located. This should give you the data with the first letter of each word capitalized. Copy this formula down the column for as far as the data goes. This column should now have the correct case. Now, select all of the cells with the formula and do an Edit/Copy. Now, select the top cell with the original data and do Edit/"Paste Special." In the dialog box select the radio button next to "Values" in the "Paste" section and press OK. The original cells should now contain the strings with the case you want, and you can delete the formulas.
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Changing Text from all CAPS

    In addition to Legare's lesson on Proper functions (thanks), the way I approached this one time used Word.
    1. from excel, copy the cells you want to convert to the clipboard.
    2. paste them into Word; this will create a table with the number of rows and columns the same as your Excel selection
    3. select the entire table.
    4. either choose a or b depending on your rqmt for conversion:
    a. use SHIFT+F3 to cycle thru a limited number of conversions
    b. select Format | Change Case which will give you a larger set of conversions; choose the one you want and click OK
    I think either of these give a little more flexibility than Excel's PROPER function.
    5. with the Word table still selected copy to the clipboard
    6. back in Excel, select the upper left cell of your original selection and paste the clipboard. this will replace the entire range with the "table" from Word, which just happens to be the same size as your original selection from step 1.

    proper use of proper tools - of course, if you don't have Word...

    Fred

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Text from all CAPS

    Select the cells you want to convert to Proper then run this macro:

    Sub Proper()

    For Each cel In Selection
    gg = cel.Value
    cel.Value = "=Proper(""" & gg & """)"

    Next cel

    End Sub

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re:Changing CAPS Plus another prob

    Thanks one and all - I used Michaels answer and it worked a treat - the database looks great now except for Some updates within the program MYOB have added extra fields into them. ie address now has 4 fields instead of 2. How can I get an excel macro to read a cell and if it meets certain criteria to take that information and place it into another cell eg if the cell has West Perth Wa 6005 in it, I would like the West Perth into one cell, Wa to be WA in another cell with 6005 in yet another cell.
    Are there any basic macro books out there that an Idiot could read?

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re:Changing CAPS Plus another prob

    Assuming your fields are separated by spaces, this macro will do what you want. First make sure you have enough blank columns to receive the parsed addresses, then select all the addresses, then run the macro.

    After you tell it how many fields you want for the State, postcode, etc., it starts at the end of each address and runs backwards until it reaches a space, puts this string in a new cell, then repeats the process until it has filled the number of fields chosen. It then puts whatever is left in the column next to the unparsed addresses.

    Sub AddressParse()
    '
    Do Until NumFields >= 1
    NumFields = (InputBox("How many fields AFTER the city name?"))
    Loop

    Set selrange = Selection
    For Each Cel In selrange

    gg = Cel.Value
    Cel.Value = "=Trim(""" & gg & """)" 'removes excess spaces

    m = Val(NumFields)
    p = m + 1
    sl = Len(Cel.Value)
    For n = sl To 1 Step -1
    r = sl - n

    If Mid(Cel.Value, n, 1) = " " Then
    Cel.offset(0, p).Value = Mid(Cel.Value, n + 1, sl - n)
    sl = n - 1
    m = m - 1
    p = p - 1
    If m = 0 Or n = 1 Then
    Cel.offset(0, p).Value = Mid(Cel.Value, 1, n - 1)
    n = 1
    End If
    End If

    Next n

    Next Cel
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •