Make a Shorter Excel Formula
January 21, 2020
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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.
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.
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.
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!)
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.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200121ctx.html
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.
Last updated: January 22, 2020 11:25 AM