Contextures News 20200121

Make a Shorter Excel Formula

January 21, 2020

A shorter Excel formula, pivot table refresh, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Shorter Formula

One of my YouTube videos shows how to reverse names from "Smith, Mary" to "Mary Smith", by using this very long formula. Do you have a different formula, or a shorter one, that does the same thing?

=RIGHT(A2,LEN(A2) - (SEARCH(",",A2,1) + 1)) & " " & LEFT(A2,SEARCH(",",A2,1) -1)

On the YouTube page for my video, someone suggested a much shorter formula:

=MID(A2&" "&A2, FIND(" ",A2)+1, LEN(A2)-1)

That short formula worked on simple names, but failed if the last name contains a space, such as Garcia Lopez. So be careful, if you decide to use it.

change names to first last

Update: Thanks to Alex Blakenburg, who sent this change to the short formula, so it works with all the names:

=MID(A2&" "&A2, FIND(",",A2)+2, LEN(A2)-1)

See other ways to split names and reverse names on the How to Split Names page on my Contextures site. There's a sample file to download too.

On Contextures

Recent items from my Contextures site and blog.

Pivot Table Refresh: This new page on my Contextures site show different ways to refresh a pivot table, after you update the source data. Do a quick manual refresh, or change the settings for an automatic refresh. There are macros too, but use those carefully, or you'll slow down the workbook.

NOTE: If worksheets are protected, check out the tips on the Pivot Table Protection page.

set option to refresh pivot table

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Data Viz: Evelina Judeikyte shares a list of the resources she used last year, while improving her data visualization skills. And as she says, "Nothing beats learning by doing. In repetition." via David Napoli (Level-Int/Adv)

Excel Tips: The FireEye blog shows a few tricks for cleaning up data in Excel. The intro paragraphs are filled with tech talk, so skip down to the formula examples. (Level -Int)

Excel Humour: See what people said about Excel recently. Do you spend more time with Excel than with your family? (Most days, I do!)

Also see: My Excel Products || Previous Issues

Old Technology

We had snowy weather on the weekend, but not as much as they got in Newfoundland (76 cm/30"). Stuck indoors, I cleaned out my office closet, and found my old BlackBerry phone - did you ever have one of those? It's so small, compared to my current iPhone. How much bigger will our phones get, before we swing back to smaller sizes? Coincidentally, Sunday was the 20th anniversary of the original BlackBerry release date, January 19, 1999.

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
[email protected]

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info


Last updated: January 22, 2020 11:25 AM