Word endings

[nickharr]

Hi - is there any formula that could detect the last two letters in a word and depending on what those letters are, substitue with other letters? They are Spanish words I'm working with. So, if the word in A1 was "hablar" and in A2 I wanted "hablas", I would use the LEFT expression to remove and replace the last two letters. This would be okay if all the words finished with the same two letters but some don't! Therefore in A3 I might have the word "abrir" and this needs to change in A4 to "abres". Would there be a formula to cover - if the word in A1 or A3 ends in "ar" replace with "as" or if it ends in "ir" replace with "es"? I'm not able to work this out at all so any help would be gratefully recieved!

 

[Fluff]

How about
=LEFT(A2,LEN(A2)-2)&IF(RIGHT(A2,2)="ar","as",IF(RIGHT(A2,2)="ir","es",RIGHT(A2,2)))

 

[lrobbo314]

Totally different formula than Fluff, lol.

Book1
	A	B
1	Hablar	Hablas
2	Abrir	Abres
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=IF(MID(A1,LEN(A1)-1,1)="a",REPLACE(A1,LEN(A1)-1,2,"as"),REPLACE(A1,LEN(A1)-1,2,"es"))

 

[Fluff]

If you have a lot of these to change, you could use a lookup table like

Book1
	A	B	C					H	I
1
2	hablar	hablas
3	abrir	abres						ar	as
4	hablas	hablar						ir	es
5	abres	abrir						as	ar
6	nothing	nothing						es	ir
HPV

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(A2,LEN(A2)-2)&IFERROR(VLOOKUP(RIGHT(A2,2),$H$3:$I$6,2,0),RIGHT(A2,2))

 

[DanteAmor]

Other option

Another option considering the infinitive "er", although you will have some problems with some verbs, for example "ir" ("vas"), "ser" ("eres")

Book1
	A	B
1
2	hablar	hablas
3	abrir	abres
4	comer	comes
5	palabra	palabra
6	some	some
7	chale	chale
8	ir	es
9	ser	ses
Hoja2

Cell Formulas
Range	Formula
B2:B9	B2	=LEFT(A2,LEN(A2)-2)&SUBSTITUTE(LOOKUP(RIGHT(A2,2),{"ar","er","ir","??"},{"as","es","es","@"}),"@",RIGHT(A2,2))

 

[nickharr]

Wow guys! Thank you so much! I've just tried a couple of your suggestions and they work perfectly for what I'm trying to achieve. I need a bit if time to work out which method will be best for me but I really appreciate the time you have all taken to reply. Many thanks.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Fluff]

You're welcome & thanks for the feedback

 

[jasonb75]

=LEFT(A2,LEN(A2)-2)&SUBSTITUTE(LOOKUP(RIGHT(A2,2),{"ar","er","ir","??"},{"as","es","es","@"}),"@",RIGHT(A2,2))

Not sure that Dante's formula will work too well if you have words with different endings not catered for in the array.
"??" might work for anything that is >"ir", but for anything in the range "as" to "eq" or "es" to "iq" the approximate match of lookup is going to pull the wrong result from the array.