Posts Tagged ‘MID() Function’

Excel and palindromes

December 28th, 2009

A palindrome is a word (or a sentence) which can be read backwards and doesn’t loose it’s meaning. Actually, the sentence means exactly the same while read either way. A few examples:

  • So many dynamos!
  • Rise to vote, sir
  • No devil lived on
  • Do geese see God
  • Are we not drawn onward, we few, drawn onward to new era?

Let’s prepare a formula which will return TRUE when the selected string is a palindrome. In my next blog entry, based on the column with many text strings (including palindromes) we will also generate a list containing ONLY palindromes.

Our worksheet, before we type in any formulas, looks like this:

Excel and palindromes

Our list of palindromes and cells waiting to be filled up with Excel formulas

To check if a string is a palindrome I will loop through all the string’s letters (form left to right) and compare, in sequence, each letter with it’s “mirrored” equivalent. The first and the last letter have to be the same, the second and the second last has to be the same and so on.

If this condition is fulfilled it means that the string is a palindrome.

My first approach to the problem below (it’s an array formula what means that you enter it using SHITS+CTR+ENTER)

{=SUM((UPPER(MID(SUBSTITUTE(B8," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B8," ","")))),1))=UPPER(MID(SUBSTITUTE(B8," ",""),ABS(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B8," ",""))))-(LEN(SUBSTITUTE(B8," ",""))+1)),1)))*1)=LEN(SUBSTITUTE(B8," ",""))}

The cell B8 contains the string to be analyzed.

How does it work?

As I said earlier, the formula compares each letter from the beginning of the string with the symmetrically located letter from the end. If two letters are the same the formula returns TRUE for each comparison.

Each logic value is then multiplied by 1 (to get a number which could be used in mathematical calculations) .

Finally the formula adds up all the numbers. If the sum is equal to the length of the string it means that we have just discovered a palindrome. I hope that the picture below will help you a little in understanding what I am talking about.

Excel and palindromes
To make the formula uninfluenced by the size of the letter (upper/lowercase) I used function UPPER to convert all letters into uppercase before I start comparing them. I have also removed all spaces from the string using function SUBSTITUTE.

Imperfections

1.

First of all the formula doesn’t seem to work properly with strings containing periods and commas. You can see it on the picture below.

Excel and palindromes

Formula doesn't work properly with sentences containing periods and commas.

In all cases with single words or sentences without any punctuation the formula returns correct results. But in other cases (I marked it red) the output is wrong. The problem can be solved by removing from all strings not only spaces, but also commas and periods.

To do so, we will replace function

SUBSTITUTE(B8," ","")

used in the above formula with

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B8," ",""),",",""),".","")

The problem with this approach is that there are too many nested formulas and Excel (at least in version 2003) will return an error. We could think about a better algorithm, but since we already started with the method described we will trick Excel a little. Let’s create a defined name “ourstring” which will refer to the formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B8," ",""),",",""),".","")

Now we will use this defined name in our formulas and Excel shouldn’t say “NO”. The results returned are now correct.

Excel and palindromes

Improved formula returns correct results.

2.

Right now, our formula slides through all letters (comparing them), from left to right and, at the same time, from right to left. As a result, the sentence characters are compared twice. When the formula starts it compares the first character with the last, and later after it reaches the end the last character is being compared again with the first one. It’s unnecessary.

So, let’s tweak our formula so it doesn’t do this extra work anymore:

={SUM((UPPER(MID(ourstring,ROW(INDIRECT("1:"&ROUND(LEN(ourstring)/2,0))),1))=UPPER(MID(ourstring,ABS(ROW(INDIRECT("1:"&ROUND(LEN(ourstring)/2,0)))-(LEN(ourstring)+1)),1)))*1)=ROUND(LEN(ourstring)/2,0)}

Have fun with palindromes!

Marcin

Separating lines from multi-line Excel cell

November 25th, 2009

A couple of weeks ago I wrote how to get rid of line breaks (inserted earlier by the shortcut ALT+ENTER) from an Excel cell. Today I would like to continue the subject of multi-line text strings in Excel cells and tell you how to separate and display only one, chosen, row of text.  It should look like this:

Separating lines from multi-line text strings in Excel

As it’s usually the case with Excel, we can approach the problem from many sides.  I set my mind on fitting everything into one formula and using Excel text function MID(), which in my example chooses and displays the correct row (other functions fulfill only an auxiliary role). My formula from the cell $D$11 looks like below:

=IF(OR(D11-1>LEN(B8)-LEN(SUBSTITUTE(B8,CHAR(10),"")),D11<1),"Given row number should be higher that 0 and lower then the total rows number",MID(B8,IF(D11=1,1,SEARCH("@",SUBSTITUTE(B8,CHAR(10),"@",D11-1))+1),IF(D11=LEN(B8)-LEN(SUBSTITUTE(B8,CHAR(10),""))+1,LEN(B8),SEARCH("@",SUBSTITUTE(B8,CHAR(10),"@",D11)))-IF(D11=1,0,SEARCH("@",SUBSTITUTE(B8,CHAR(10),"@",D11-1)))))

The cell $B$8, as you already noticed, contains our source material – text string divided into many separate rows by break a line character (inserted using the shortcut ALT+ENTER).

For all those who prefer VBA code and user defined functions, I have prepared an example of how such a function could look like. I used probably the easiest approach and took advantage of VBA SPLIT() function. The function has it’s flaws but in this simple case, should be just fine.

I named my UDF (User Defined Function) GetTextRow, and the function takes two parameters. It needs to know where (what cell) is the string we are going to use as a source data, and what row number we want to display. The result is shown on the animation below:

Separating lines from multi-line text strings in Excel
And my VBA code:

Function GetTextRow(WhereFrom As Range, _
                      RowNumber As Integer)

Dim Temporary As Long
Dim TemporaryArray As Variant

' First, lets check if the text in the pointed cell is divided into separate rows at all
' If this is not the case, we will display the whole text

Temporary = InStr(WhereFrom.Value, Chr(10))
If Temporary = 0 Then
    GetTextRow = WhereFrom.Value ' return text from pointed cell
Else
    ' lets also check if the row number the user provided is not too big.

    TemporaryArray = Split(WhereFrom.Value, Chr(10))
    If RowNumber - 1 > UBound(TemporaryArray) Or _
    RowNumber = 0 Then
        GetTextRow = "It is strongly recommended to think over the row number you used"
    Else
    ' if everything is all right the function returns (displays) the chosen row
        GetTextRow = TemporaryArray(RowNumber - 1)
    End If

End If

End Function

Marcin