[techtalk] Tech-Talk: EXCEL - Easy Way to Change Text in All Caps

Wed Sep 13 13:43:23 PDT 2017

Greetings!

The Tech-Talk e-newsletter for Oregon library workers is distributed weekly via the Libs-OR and TechTalk mailing lists. Please do not forward.

Know someone who might be interested in receiving a copy but they’re not on the Libs-OR or TechTalk mailing lists? Have them contact darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> or visit http://listsmart.osl.state.or.us/mailman/listinfo/techtalk to receive the TechTalk newsletter!

This paid subscription service is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon. Enjoy!

[http://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png]

Brought to you by the State Library of Oregon

[State of Oregon]

The State Library is pleased to provide you with Tech-Talk.com. We encourage you to make the most of this skill-building membership site at www.Tech-Talk.com<http://r20.rs6.net/tn.jsp?f=001z-L14VKrpGT3wXyUb3kjhojbFRQTkLUSMlva5jp3EAvaTFFB8N9mLNfUtILeVGaNm1LnnKTk_YGVYoRFD-Gd2__02YOj_RdgxO7TbgG3BJCwOFcGfrsOqWBxeMXoB3A4SpymUoEbhfyB0af0Zz1a7w==&c=B3GmVC1x6I-gvcco3WhBNcWDcL_D0dYZ0a-pmchJA9yTcUasg45ECA==&ch=nEMELESlPLGIUrW87YmgQnVIEQPoqru65m6Ehli2lKjnua2WSm07GA==>.

Username: ORLIBTECH
Password: ORLIBTECH (case sensitive)
Contact Darci Hanning at darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> if you have questions.

[http://files.constantcontact.com/ee1208b4001/9763e732-f4c8-421c-adac-fd34a127f046.png]<http://r20.rs6.net/tn.jsp?f=001z-L14VKrpGT3wXyUb3kjhojbFRQTkLUSMlva5jp3EAvaTFFB8N9mLHaA5V-zMJ4oEzH6J6wfzM62tKRkDAvMBS4FR7DW4-zl90c-Qg7WiW2YI_rSkNTW0LExHF4wG5TG164TQcZHTHnZu6El_WMXpt92orFFz-Q2ZIq78EuFd2Az_WwxOL3eavbDtv7D9-UeUlGz3axbX-E=&c=B3GmVC1x6I-gvcco3WhBNcWDcL_D0dYZ0a-pmchJA9yTcUasg45ECA==&ch=nEMELESlPLGIUrW87YmgQnVIEQPoqru65m6Ehli2lKjnua2WSm07GA==>

 This Week's Tech-Talk: EXCEL

1. VIDEO ...  Easy Way to Change All Cap Text
2. ARTICLE ... Easy Way to Change All Cap Text
3. COMMUNICATING ... "You're funny!"

NOTE: To view the VIDEO, click here<http://r20.rs6.net/tn.jsp?f=001z-L14VKrpGT3wXyUb3kjhojbFRQTkLUSMlva5jp3EAvaTFFB8N9mLHaA5V-zMJ4oEzH6J6wfzM62tKRkDAvMBS4FR7DW4-zl90c-Qg7WiW2YI_rSkNTW0LExHF4wG5TG164TQcZHTHnZu6El_WMXpt92orFFz-Q2ZIq78EuFd2Az_WwxOL3eavbDtv7D9-UeUlGz3axbX-E=&c=B3GmVC1x6I-gvcco3WhBNcWDcL_D0dYZ0a-pmchJA9yTcUasg45ECA==&ch=nEMELESlPLGIUrW87YmgQnVIEQPoqru65m6Ehli2lKjnua2WSm07GA==>. Enter the User Name and Password!

Tech-Talk: EXCEL - Easy Way to Change All Cap Text

[All caps]

What a day ... and a pleasant surprise ... I had last week!

There I was with a spreadsheet of over 300 entries that needed to be cleaned up for an online mail-merge. We wanted to personalize each letter with the individual's name. The problem was that the data was in ALL CAPS.

Who wants to see, "DEAR MR. POTTSWORTHY"? Clearly I have to re-type all 300 names into title case / proper case - with just the first letter capitalized. Boy, that will be time consuming!

Then I remember there is a change case option ... but when I go looking for it in EXCEL ... it isn't there. Urg! Am I really going to have to hand-type all of the 300+ names manually?

Well, after a little digging, I find that changing text case IS possible in Excel -- but you have to use a Formula. (Of course -- in Excel there's a formula for everything!)

Change Case in Excel With This Formula

When you have cells that contain text in all CAPS or lowercase, you can use a function to change the text case to either UPPER, lower or Proper case (the first letter capitalized). It's not as simple in Excel as it is in Word. There’s 3 basic steps.

1. Add a Column

First, add a temporary column next to the one that has the text in it that you would like to change the case.

[add a column]

2. Enter the Formula

In the first cell of the empty column you're going to add a formula that refers to the cell next to it (the one with all caps in this case).

[http://files.constantcontact.com/ee1208b4001/575efe0c-9355-4f4c-aa3e-10f589ebfd58.png]

·     Type in the formula =PROPER(A2) and press Enter.

·     The new column will contain the text in your A2 cell, but in Proper case.

·     To apply this formula to the whole column of cells in your spreadsheet, select the cell B2, then grab the lower right corner of the cell when it is displaying a small square (that turns into a + sign) and drag it down the column of cells to copy the formula to all cells.

[drag it down]

TIP: In this example, the cell A2 is the first in our list that we want to convert. Of course in your spreadsheet it may not be cell A2, so add the cell reference appropriately.

3. Copy the Column Contents

Now you have a column of text changed to the case you want. Since this column is actually filled with a formula, not the text itself, you can't just delete the first column with the text in all caps to get rid of it -- or you will get formula errors.

·     Instead, highlight the column of content with the new proper case text, right-click and choose copy.
·     Now highlight column A column, right-click and choose Paste Special, then Paste Special from the bottom of the window.
·     Choose to paste the Values so that it just brings over the text of the cell, not the formula.
·     Now you can delete the temporary column.

In this example we changed all CAPS to Proper case (first letter in caps), but if you want to change text case to lower or upper case you would use the formulas: LOWER(A2) or UPPER(A2).

TIP: This formula might not stick in your brain, but remember you can always go to the www.Tech-Talk.com<http://r20.rs6.net/tn.jsp?f=001z-L14VKrpGT3wXyUb3kjhojbFRQTkLUSMlva5jp3EAvaTFFB8N9mLNfUtILeVGaNm1LnnKTk_YGVYoRFD-Gd2__02YOj_RdgxO7TbgG3BJCwOFcGfrsOqWBxeMXoB3A4SpymUoEbhfyB0af0Zz1a7w==&c=B3GmVC1x6I-gvcco3WhBNcWDcL_D0dYZ0a-pmchJA9yTcUasg45ECA==&ch=nEMELESlPLGIUrW87YmgQnVIEQPoqru65m6Ehli2lKjnua2WSm07GA==> database to find it when you need it later.

A Trick for Excel ... But Using Word

Since Microsoft Word does have a change case option, you may be able to use a shortcut of copy and paste between the software programs.

If the text that you want to convert is pretty straight forward (not hundreds or thousands of rows long), try this:

[copy to Word]

·     Copy the column of text you want to convert in Excel.
·     Paste it into a new Word document.
·     Use the Change Case option in the Font section of the ribbon to make your changes.
·     Copy the text from Word and Paste it back into your Excel document.

This method is much more intuitive and easy to remember, but may be cumbersome with large amounts of content.

[http://files.constantcontact.com/ee1208b4001/f8f1ecc8-4884-4c21-8ed0-cf647e6db944.png]

Communicating: Talking
Dismiss with "You're funny"

Let's say you're talking with someone and you simply don't know what to say ... it's an awkward situation because you don't wish to agree, and yet disagreeing would cause a problem.

How do you get out of it gracefully? Sometimes people are upset and you want to diffuse the situation instead of delving into it. You want to dismiss it.

Try saying (with a pleasant smile), "You're funny!" and then move on. This way the person doesn't think you're making fun of them ... or their idea. They're just slightly confused, because you think they're being funny. No one gets upset about that.

[innovation]

 CLICK HERE ... FOR THE TECH-TALK LIBRARY!<http://r20.rs6.net/tn.jsp?f=001z-L14VKrpGT3wXyUb3kjhojbFRQTkLUSMlva5jp3EAvaTFFB8N9mLNfUtILeVGaNm1LnnKTk_YGVYoRFD-Gd2__02YOj_RdgxO7TbgG3BJCwOFcGfrsOqWBxeMXoB3A4SpymUoEbhfyB0af0Zz1a7w==&c=B3GmVC1x6I-gvcco3WhBNcWDcL_D0dYZ0a-pmchJA9yTcUasg45ECA==&ch=nEMELESlPLGIUrW87YmgQnVIEQPoqru65m6Ehli2lKjnua2WSm07GA==>
Your Membership Site!

NOTE: Forgot your user ID? See your sponsor block at the top of this email.
Problems? Contact us at Support at Tech-Talk.com<mailto:Support at Tech-Talk.com>

 Do you have an idea for an article, or a problem using technology in some way...

We can help!

Just contact us and we'll get the answer for you right away. Click here<http://r20.rs6.net/tn.jsp?f=001z-L14VKrpGT3wXyUb3kjhojbFRQTkLUSMlva5jp3EAvaTFFB8N9mLF0EiylYeJSHR4L1HA4ZzwLwpLZeYhW-AMUDhZ3ydaFdTToqGeEv5xchfqnqqkNG37X4G5LcClUIeYLxlUjU9N8AoHHX1yXrnkYXdUgPXBrH7Nt-gPITvCQ=&c=B3GmVC1x6I-gvcco3WhBNcWDcL_D0dYZ0a-pmchJA9yTcUasg45ECA==&ch=nEMELESlPLGIUrW87YmgQnVIEQPoqru65m6Ehli2lKjnua2WSm07GA==>!

[http://files.constantcontact.com/ee1208b4001/464c50f5-0d77-4705-bb24-bf95437ce585.jpg]

Copyright 1996-2017 Shared Results International. Published weekly. Do not forward this issue of Tech-Talk.com without prior permission from Shared Results International. Distribution is limited by contract. Forwarding it to unauthorized recipients constitutes copyright infringement. For information on site licenses or how to include additional recipients, email darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>.

Cheers,

Darci Hanning, MLIS
Technology Development Consultant
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> | 503-378-2527 | www.oregon.gov/osl/ld<http://www.oregon.gov/osl/ld>
Follow us: Facebook<http://fb.me/orlibsupport> | Twitter<https://twitter.com/ORLibSupport> | Tumblr<http://www.statelibraryor.tumblr.com/> | Pinterest<https://pinterest.com/statelibraryor/>
Ask me about the Edge Initiative<http://oregonedge.net/>!
[cid:image005.png at 01D31C27.4B9FEA30]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20170913/dd965a3b/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20170913/dd965a3b/attachment.png>