A simple (I'm pretty sure) Excel question

Posted by: tanstaafl.

A simple (I'm pretty sure) Excel question - 26/03/2010 20:41

Is there a function in Excel that will allow me to look at just the last two letters of a text string, and perform an operation based on what those letters are?

In other words, if I am in cell E12, I want to do something like this:

if [last_two_letters_C12]=ar,[append "áste" to the contents of E12],
if [last_two_letters_C12]=er,[append "íste" to the contents of E12],
if [last_two_letters_C12]=ir, [append "íste" to the contents of E12]

What I don't know how to do is parse out the last two letters of C12 to operate my if... statement.

Is there a simple way to do this? (Anybody who has had a year of high school español will know what I am trying to do here.)

tanstaafl.

Posted by: Geoff

Re: A simple (I'm pretty sure) Excel question - 26/03/2010 21:20

RIGHT(C12,2) should give the last 2 characters of the contents of C12, shouldn't it?

Posted by: tanstaafl.

Re: A simple (I'm pretty sure) Excel question - 26/03/2010 22:09

Originally Posted By: Geoff

RIGHT(C12,2) should give the last 2 characters of the contents of C12, shouldn't it?

I dunno... that's why I was asking!

I'll give it a try and see what happens.

Thanks.

tanstaafl.

edit: Yes, indeed it does. Now I'll see if I can figure out the rest of it...

db

edit2: Well, it didn't take me long to figure out I was over my head again. How can I put a string into E12 that is C12 minus the last two characters, plus "áste" ?

In other words, if E12 contained the string Hablar, how would I convince cell E12 to contain the string Habláste, but only if the last two letters of E12 = "ar" ?

db

Posted by: Geoff

Re: A simple (I'm pretty sure) Excel question - 26/03/2010 22:41

I wasn't entirely sure either, I checked it in Openoffice, not Excel

Can a formula append text to the contents of its own cell? Would that not introduce a circular reference?

It seems to me that an additional column is needed, say in F12, with something like:

Code:

=if(right(c12,2)="ar",concatenate(e12,"áste"),if(right(c12,2)="er",concatenate(e12,"íste"),if(right(c12,2)="ir",concatenate(e12,"íste"),e12)))

Then you could hide column E.

My Excel is very rusty though, so there may be a much better solution.

Edit: a better solution like a VBA macro, but my knowledge of those is even more rusty!

Posted by: tanstaafl.

Re: A simple (I'm pretty sure) Excel question - 27/03/2010 00:53

Originally Posted By: Geoff

circular reference?

Doh! Of course, you are right.

After giving it some thought, I think I can rephrase the problem (taking a different approach) so that it is possible. Maybe.

We have a string (Hablar, for example) in C12. The formula in E12 will check C12 to see if the string ends in "ar", and if it does it writes that string in C12 minus the "ar" ending and appends the "áste" string to it. (If C12 ended in "er" or "ir" it would append the appropriate string instead of "áste".)

A simple formula like

=if(right(c12,2)="ar",C12,"")

is not circular, but when we start getting fancy by writing only part of C12 to the destination cell and then appending something to it, I'm not sure what will happen.

tanstaafl.

Posted by: gbeer

Re: A simple (I'm pretty sure) Excel question - 27/03/2010 02:19

Code:

Private Sub Worksheet_Calculate()
        For Each C In Worksheets("Sheet1").Range("test").Cells
            C.Value = Right(C.Value, 2)
            
        Next
        
End

My install of Excel is broken, the help for VBA isn't working. But the above is a start.

If you rt-click on the sheet's tab, choose view code, and put the above in the code for that sheet. It will run whenever the spreadsheet recalculates. All it does is trim the contents of the cells in the named range to have only the last 2 chars. (you need to have a range named test predefined)

To complete this the comparison and revision code needs to be added and a suspension and resumption of auto recalculation has to be added, as this code runs endlessly. esc will exit the loop.

Posted by: wfaulk

Re: A simple (I'm pretty sure) Excel question - 27/03/2010 03:09

If your input is in A1 and the thing you want to modify is in B1, then this works (in OpenOffice.org, anyway):

Code:

=IF(EXACT(RIGHT(A1;2);"ar");CONCATENATE(B1;"aste");IF(OR(EXACT(RIGHT(A1;2);"er");EXACT(RIGHT(A1;2);"ir"));CONCATENATE(B1;"iste");B1))

You have to put it in a separate cell. There's no way to modify the existing cell, AFAIK.

Posted by: gbeer

Re: A simple (I'm pretty sure) Excel question - 28/03/2010 01:17

Code:

Private Sub Worksheet_Change(ByVal words As Range)

        For Each C In Worksheets("Sheet1").Range("words").Cells
            If Right(C.Value, 2) = "ar" Then
                C.Value = Left(C.Value, (Len(C.Value) - 2)) + "áste"
            ElseIf Right(C.Value, 2) = "er" Then
                C.Value = Left(C.Value, (Len(C.Value) - 2)) + "íste"
            ElseIf Right(C.Value, 2) = "ir" Then
                C.Value = Left(C.Value, (Len(C.Value) - 2)) + "íste"
            End If
        Next
        
End Sub

Instructions:
In the worksheet select the range with all the text to be updated.
Name that range "words".

Select the worksheet's tab and rmb>view code.
Paste the above code into the window. Note: the name "Sheet1" in the code should be changed to match the name of the specific worksheet.

After that any change to a cell in the range "test" will trigger a check of the all the cells in the range "words" and make the replacements specified.

Posted by: tanstaafl.

Re: A simple (I'm pretty sure) Excel question - 28/03/2010 18:34

Originally Posted By: wfaulk

You have to put it in a separate cell. There's no way to modify the existing cell, AFAIK.

This is the last time I will ever be so naive as to think my Excel question might be simple!

Anyway, I went to my Excel Guru Extraordinaire (does anyone besides me feel the absence of Paul Grzelak on this bbs?) and here is the answer:

=IF(RIGHT(B3,2)="ar",LEFT(B3,LEN(B3)-2)&"áis",IF(RIGHT(B3,2)="er",LEFT(B3,LEN(B3)-2)
&"éis",IF(RIGHT(B3,2)="ir",LEFT(B3,LEN(B3)-2)&"ís","Notstandard")))

the key elements I was lacking being the "(LEN(B3 -2)" conceept, and the "&" function for appending the áis etc. suffixes.

Now I just have to work out how I am going to put the formula in its 18 variations (for 1st, 2nd, 3rd person singular and plural in present, past, and future tenses) into the spreadsheet in such a fashion that I can copy it and have the "B3" update to B7, B11, B15 etc. and still allow me to sort the list. Never mind, don't try to visualize that, without seeing the actual spreadsheet you can't make any sense of that.

At least now the problems are in the realm that I can understand.

tanstaafl.

Posted by: gbeer

Re: A simple (I'm pretty sure) Excel question - 28/03/2010 21:58

Bitt is right. Formulas (functions) are not permitted to write to other cells. Nor can macros.

Excel only allows such things to happen in specific ways.

See this page for the long description. The exception noted is the one used in the VBA code I supplied.

Posted by: gbeer

Re: A simple (I'm pretty sure) Excel question - 28/03/2010 22:08

You are not, by any chance, using Excel 2008 for Mac?

Posted by: tanstaafl.

Re: A simple (I'm pretty sure) Excel question - 29/03/2010 00:30

Originally Posted By: gbeer

You are not, by any chance, using Excel 2008 for Mac?

No. I detest Macintosh computers. (Sorry, Bruno...) They're different from what I'm used to, so obviously they can't be any good.

I'm using Excel 2002.

See the attached file for the end result of my quest. Enter a verb and see what happens.

tanstaafl.