Last word in a string with an Array formula


EXCEL UNPLUGGED

As promised in the Pragmatism vs. Purity in Excel article from last week, this week’s article will explain the following array formula

{ =MID(A1,MAX((–(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=” “))*ROW(INDIRECT(“1:”&LEN(A1))))+1,50)}

Throughout this article, keep in mind that this is an Array formula and should be entered with Ctrl+Shift+Enter. Also, it should only be entered into one cell. The pictures bellow will show multiple cells so that you can see the whole result where one cell would not show it.

We will start at the heart of the formula

{ =MID(A1,MAX((–(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=” “))*ROW(INDIRECT(“1:”&LEN(A1))))+1,50)}

What this part actually does is it breaks the string apart into single letters

Here’s how it’s done.

=MID(“string”,starting position,number of characters)

The MID function takes the string given in the first argument and starting at the position provided by the second argument returns the number of characters defined by the third argument. So =MID(“Paul McCartney”,8,4) gives you “Cart”.

But the trick…

View original post 353 more words