Printable View
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?
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.
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.
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:
The above code converts the Last name with first letter as capital and the rest in lower case.Code:Private Sub LastName_AfterUpdate()
LastName = StrConv(LastName, vbProperCase)
End Sub