Excel Unplugged

Last word in a string with an Array formula

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

Last word in a string with an Array formula

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 is to replace a fixed number in the second argument with the ROW(INDIRECT(“1:”&LEN(A1))). The INDIRECT function returns 1:13 in the sample above since it concatenates “1:” and LEN(A1) which returns the number of characters in the string. In the sample above that is 13. Now the ROW takes over and turns =ROW(1:13) into {1,2,3,4,5,6,7,8,9,10,11,12,13}. So a vector of sequential numbers from one to thirteen (or whatever the length of the string in A1 is).

So the function =MID(“One Two Three”,{1,2,3,4,5,6,7,8,9,10,11,12,13},1) actually returns {O,n,e, ,T,w,o, ,T,h,r,e,e}

Then we take this characters and compare them with ” ” (Space). This is prudent since our goal is to find the last space and therefor the beginning of the last word. After this is done, we get {FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE}.

Last word in a string with an Array formula

Now we need those TRUE and FALSE values as 1 and 0. So we do a (–()) trick.

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

 

Last word in a string with an Array formula

Then we do a multiplication of our {0,0,0,1,0,0,0,1,0,0,0,0,0} with {1,2,3,4,5,6,7,8,9,10,11,12,13} like so

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

And we get something very useful.

Last word in a string with an Array formula

A {0,0,0,4,0,0,0,8,0,0,0,0,0} vector. Now since our goal is to find the placement of the last space, we just dress this vector in a MAX function.

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

Last word in a string with an Array formula

And there it is, the last space is the eight character in our string.

Now all we have to do is to dress this into another MID which will take the string in A1, start on position 8+1 (so not with the space but with the next character) and from there, take 50 characters.

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

The trick is to use a number that is bigger than the longest string you can imagine. This is a well-known trick in Excel that often gives you the ability to use MID instead of RIGHT, and it comes very handy in this situation. Ant this formula gives us

Last word in a string with an Array formula

This is by no means the easiest way to do this but in Excel purist’s eyes, it’s very, very near to eternal happiness. For a great function that also gives the same result, you can look at my previous post where MF suggested it in his comment.