Excel Trick: Converting Text Case

So I had this excel sheet of contacts registered for an event.
It was to be imported as a contact list in google contacts, but the attendees' name columns had all typed in uppercase.

And I hate uppercase in contact list. IT UNNECESSARILY MAKES THE LIST LOOK SO BIG AND LONG....

Hence I was looking for that classic button "Aa" to change cases, just like in MS Word. But after wasting some time I realized that it is not there in MS Excel. Does MS think that no one would need this facility in MS Excel? How foolish? What to do now?

So I just

1. copied the names column in MS Excel
2. copied it in MS Word
3. changed the case and
4. copied from MS Word and pasted it back to MS Excel in the same column

That did the work, easily! but the wrong way!


But wait, how can MS be so stupid to include this simple thing in one product but not in other, while both products deal with large amount of text? I wanted to find out, and then I found out!

Some great mathematician/scientist at MS thought that since there are so many formulas in Excel, the case change must also be done via a formula, otherwise, you know, what a shame it would be if just clicking a button achieves it.

लोग क्या कहेंगे ?? 😱
Gates job Microsoft, yet again!

So the way you must change cases in excel is as follows:

1. Type a formula "=Proper(<first cell number with uppercase name>)" in an empty column.
2. Drag the formula down so that it is copied to all the remaining cells in the same column.
3. You have converted all the cases. Now,
4. Copy text from the formula column.
5. "Paste by value" in the actual column which had names in uppercase.


Comments

Post a Comment

Popular posts from this blog

Hello !! GOD here…

Image
During my childhood, as a family trip we were visiting a well known temple in Himachal Pradesh. While I was moving away after praying, a pujariji came and said, “kuchh chadhao”. I looked at him, wondered, and said, “mere paas toh kuchh nahi hai”. Seeing that my parents were outside and no one was close to us, he stared at me with his big-red eyes and almost shouted, “kahan se aaya hai”? I was so scared that I couldn’t stand there for just a second. I just ran! They say, “GOD is everywhere”… but I doubt because if that’s true, then what purpose do temples serve here? And if HE lives in temples, how can HE be everywhere? Why one has to go to a temple (or a mosque or a church for that matter) for prayers? A confusing cycle of questions… and these questions arise in my mind due to these incidences, which often take place with one while visiting temples. In another recent incidence, while doing parikrama in a famous temple in AP, a pujariji came to me, put a small pudiya in my palm and...
Read more

Investing in Direct Mutual Funds: Zerodha Coin vs Kuvera

Image
Ok, so i had been thinking about it since a long time... whether to migrate to Kuvera from Coin  for my direct mutual funds investments. The reasons: (1) Coin/Zerodha charges ₹300 Annual fee + ₹50 per month, whereas Kuvera is completely free (2) Kuvera makes investment recommendations suitable to investor's risk appetite and investment objectives. This is completely missing in Coin. Both reasons were compelling, but I wasn't sure of the hassle it would require to move existing mutual fund investments to Kuvera platform. Then today, I see this on my Coin dashboard: I then read more about it here:  https://zerodha.com/z-connect/featured/coin-is-now-completely-free And it made me happy to save ₹600 per year for direct mutual fund investments. (Well, the August 2018 fee is already deducted, so the savings start from September 2018) Now, reason (2) still holds true, I hope Coin addresses that too. 😛 Of course Zerodha has an added advantage of a linked trading/de...
Read more

My Blog on Linux Kernel

Wow!!! I and Shweta have launched it at http://insidelinuxkernel.blogspot.com/ successfully, after much thinking. The second post (which will be the first kernel related post) has already taken shape. We are looking forward to a good learning with great fun. Wish Us Luck…
Read more

using vim inside screen session

I have been facing this error so often now. Whenever I am logged into a remote machine in a screen session, the vim doesn't work. $ vim /etc/bash.rc error vim terminal: capability "cm" required After some research, I could fix it as follows. When this or any other error comes up while using vim in a screen session, first immediately check what is TERM set to. In my case, it was xterm-screen $ echo $TERM screen.xterm-256color Change it as follows and you should be good to use vim without any issue. $ export TERM=xterm $ echo $TERM xterm Now, vim works like charm. 👐
Read more

DellEMC Jobs | Bangalore/Pune | Filesystem, Storage, C/C++, Dev/QA | 10 Sep 2018

Many open positions in DellEMC Pune and Bangalore offices. You can contact me at jai[dot]gahlot[at]gmail[dot]com for pursuing any of these. DPD R46197 Software Quality Principal Engineer I8 ·        File system Experience , Storage and backup domain. ·        Automation with Python ·        Familiarity with technologies like deduplication, replication and HA. ·        Programming skills. Any scripting language such as perl/python/ruby ·        Possesses strong storage product/technology/industry knowledge Bangalore/Pune DPD R65884 Software Quality Senior Engineer I7 ·        File system Experience , Storage and backup domain. ·        Automation with Python ·        Familiarity with technologies like deduplication, replication and HA. ·        Programming skills. Any scripting language su...
Read more

What's with this Bakery's Aroma in Malls

Image
Every time you pass by that bakery in a shopping mall, your nose is filled with a refreshing fragrance of freshly baked donuts and you can't help yourself being dragged to the bakery shop and buy a quick bite! Good for your nose and the bakery owner, but bad for both- your pocket, and health. Ever wondered does the bakery really keeps baking cookies? You don't see those many people buying, do you? Well the answer is: No, it is just the gimmick played by the bakeries to attract you to the shop. Here is how to produce the fragrance of a fresh baked donut. 1. Boil water in a pan. 2. Put 10ml vanilla essence in the boiling water 3. Let it boil for 15-20 mins And there you go. Your home starts smelling like a bakery. Do it now, thank me later! 😉 And be ready to share some cookies with your neighbors, because they will come running! 😋 There are baking fragrances available in stores which you can easily shop, but that in my opinion is an overkill!
Read more

Lost in Linux Kernel

It has been a significant time since I had started studying Linux kernel . I started at about the beginning of the year and till now I have not got confidence on the subject. Something is missing. Proper guidance for sure and may be the discussions also. The only mate I have got for discussions is Shweta, and having started recently, she also doesn’t know much. The other problem is with my memory. I had thought of preparing notes but... when I start writing, every line of the book seems worth noting down. Now thinking of writing a technical blog on whatever I learn in Linux Kernel, but haven’t locked that idea yet. May be, that way I would be able to arrange my thoughts properly. Explaining something always helps one to learn more. Because the thinking one develops on something while explaining, he never develops while reading himself. The idea sounds good, but far-easier said than done. What to write about, how to write about, who to seek help from and the most important thing is how ...
Read more

in one line: block storage vs file storage vs object storage

I was really confused until a colleague explained it to me! So here it is, in extremely simple terms: Block Storage: Application/user reads/writes blocks, constructs file itself. E.g. normal hard drive or SAN storage. File Storage: Application/user reads/writes a particular file path in its entirety (typically using SMB/NFS/FTP/HTTP protocols) without worrying about the filesystem where it is stored. E.g. A block storage mounted as NFS on a remote server is a file storage for that remote server. Object Storage: Application/user reads/writes a particular file without worrying about where it is stored in the directory hierarchy. The access is always made to the basename of the file in question. The object storage has to locate the file and provide access. Typically this is done by storing the SHA of file as well. The file is always located using this SHA value. This also implies that any change in the file contents results in a new SHA value and hence the object storage treats as stor...
Read more