Results 1 to 4 of 4

Thread: Excel: format data capitalization

  1. #1
    Join Date
    Jun 2009
    Posts
    441

    Excel: format data capitalization

    I imported a database file from mysql on my computer. I opened it with Excel to view the data in it. The data was there but it was irritating me. It includes entries which was pretty messed like "aLok", "ChanDresh", etc. Is it possible to bring all this data in a common format like "Alok", "Chandresh", etc? Can I format data capitalization in Excel?

  2. #2
    Join Date
    May 2008
    Posts
    2,945

    Re: Excel: format data capitalization

    Excel include an inbuilt formula known as "PROPER", "LOWER", "UPPER" which formats your entries based on your requirements. Among this "PROPER" is the one that you need to format your data as you desire.

    In a new column, type "=PROPER(value)" where "value" implies the cell or range of cells that you would like to format.

    You can also use "=LOWER(value)" to lower the case of the entry and "=UPPER(value)" to upper the case of the entry.

  3. #3
    Join Date
    May 2008
    Posts
    913

    Re: Excel: format data capitalization

    Excel includes many Text functions that allows you to format your text entries in a worksheet as per your requirement. One of this function includes PROPER which converts the entries in such a manner that only the initial letter of each word is in uppercase. The PROPER function capitalizes not only the first letter of each word but also any other letters in the text that do not follow another letter.

    So if any cell contain "viRUs" then it will convert to "Virus" with the PROPER function.

  4. #4
    Join Date
    May 2008
    Posts
    1,812

    Re: Excel: format data capitalization

    The StrConv function can be used to change the case of a string to uppercase, lowercase, or so that the first letter is uppercase. The syntax is StrConv(string, conversion), where "string" is the text string and "conversion" is 1, 2, or 3. For "conversion," uppercase is 1, lowercase is 2, and 3 makes the first letter of each word uppercase. Instead of the integers 1, 2, or 3, you can also use one of the following constants:

    vbUpperCase - Converts the entire string to uppercase.
    vbLowerCase - Converts the entire string to lowercase.
    vbProperCase - Converts the first letter of each word to uppercase and the remaining letters to lowercase.

    For example:
    Code:
    Private Sub LastName_AfterUpdate()
    LastName = StrConv(LastName, vbProperCase)
    End Sub
    The above code converts the Last name with first letter as capital and the rest in lower case.

Similar Threads

  1. Excel and capitalization
    By Deabelos in forum Windows Software
    Replies: 4
    Last Post: 16-01-2012, 08:01 PM
  2. data transfer from tally to excel in a modified format.
    By jasghuman in forum Windows Software
    Replies: 1
    Last Post: 22-08-2011, 04:44 PM
  3. Replies: 19
    Last Post: 17-11-2010, 04:19 PM
  4. How to ASP.NET data will be output directly into Excel format
    By Savannah87 in forum Software Development
    Replies: 4
    Last Post: 12-02-2010, 04:58 AM
  5. Replies: 6
    Last Post: 25-07-2009, 12:01 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,660,885,723.88980 seconds with 17 queries