- Gašper Kamenšek
- November 11, 2014
- 4 Comments

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 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}.

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)}

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.

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)}

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

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.

Sum up the diagonal of your data anywhere on a Sheet

- January 26, 2016

The title of this post is actually a question I got on one of the ...

Convert Text to Numbers in Excel

- July 29, 2014

A task that comes about every so often in Excel and can be quite ...

Sum of ten largest values

- June 24, 2014

This will be a short lesson on three great and sadly unknown ...

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Nice article!

And thanks for mentioning! 🙂

A friendly reminder:

Seems that WP turns the – – (double negative) into a single dash. The (–()) trick is actually (- – ()) ‘no space in between of course.

Reblogged this on SutoCom Solutions.

That’s a beefy formula.

I do this with a simple VBA vunction:

Function LastWord(Str1 As Variant) As Variant

‘========================================================

‘ This function returns the last word of a string.

‘ It does this by looking for a space from the right side of a string

‘ and returning everything to the right of that.

‘

‘ Example:

‘ Cell D1 has “This text is NICE”

‘ Cell D2 has =LastWord(D1) and will return ‘NICE’

‘========================================================

Dim i, j, k As Integer

k = 0

j = Len(Str1)

k = InStrRev(Str1, ” “)

LastWord = Mid(Str1, k + 1, 100)

End Function

Two other ways to extract the last word, but without needing an array formula are:

=MID(A1,SEARCH(“^”,SUBSTITUTE(A1,” “,”^”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))+1,50) and

=TRIM(RIGHT(SUBSTITUTE(AL28,” “,REPT(” “,20)),20))

That array function of ROW is interesting, though!