Select only words with an accent

[deciog]

Gentlemen, good morning.

I need all accented words from this column to be extracted and copied to another adjacent column as per Template

Important only in formulas.

Thank you very much in advance

Hugs

 

[bosco_yip]

This formula work for Excel 2013 and above

In G3, formula copied down :

=IFERROR(INDEX($C$3:$C$18,AGGREGATE(15,6,ROW($C$3:$C$18)-ROW($C$2)/ISNUMBER(FIND("%",ENCODEURL(SUBSTITUTE($C$3:$C$18," ","")))),ROW(A1))),"")

 

[deciog]

Bosco

I didn't know this ENCODEURL function, it's great,
Thank you very much, it worked perfectly

A big hug

Decio

 

[bosco_yip]

Or, In Excel 2021, using:

=FILTER($C$3:$C$18,1-ISERR(FIND("%",ENCODEURL(SUBSTITUTE($C$3:$C$18," ",)))),"")

Regards

 

[Peter Bartholomew]

I set out to solve the challenge using CODE to pick out characters later than "z" and wrapping the core calculations within Lambda functions.
First though, I tried Bosco's ingenious idea of using ENCODEURL.. Sadly, I forgot the 'space' character, but one can't get it right every time!

I started with the explode function that splits a string into individual characters,

Explodeλ
= LAMBDA(string,
     MID(string,
        SEQUENCE(1,LEN(string)),
     1)
  );

It should be a built-in function in its own right, or included within TEXTSPLIT, but sadly it's not. Then I used the function within ,HasAccentλ, to test whether an accented character appears within each word.

HasAccentλ
= LAMBDA(name,
    OR(CODE(Explodeλ(name))>122)
  );

Finally, a further Lambda to MAP the list to individual words and apply the FILTER. I tried to look up 'accented' in Portuguese but it may mean 'sounds like a Mexican citizen' rather than 'is an accented character'.

Acentuadoλ
= LAMBDA(list,
    FILTER(list, MAP(list, HasAccentλ))
  );

With that, the worksheet formula was

= Acentuadoλ(list)

 

[GraH - Guido]

Bosco, really, "ENCODEURL" ? =

 

[deciog]

Bosco, good morning.

It also worked perfectly

Thanks

Peter, Good morning.

I really enjoyed seeing the sequence for the solution of the result

Worked perfectly

Thanks

Decio

 

[bosco_yip]

Bosco, really, "ENCODEURL" ? =

@GraH - Guido , @deciog

For your more understand of ENCODEURL function, I give a simple example as follow:

To extract number from a string taken from web [ that mean text contains web blank Char(160) ]

1] In A3, enter: ="42000"&CHAR(160)&"Discounts given"

2] Formula 1 in B2: =0+LEFT(A2,FIND(" ",A2)-1)

3] Formula 2 in C2: =0+LEFT(A2,FIND("%",ENCODEURL(A2))-1)

4] B3, return "#VALUE!"

 

[deciog]

Bosco

That's why I like this forum.
I learn a lot from you, Bosco thank you very much for your dedication and the humility of teaching.

A strong hug

Decio

 

[GraH - Guido]

@GraH - Guido , @deciog

For your more understand of ENCODEURL function, I give a simple example as follow:

Thanks for the explanation, Bosco. I got as far by doing the formula auditing. Still it's a stroke of genius to use ENCODEURL like this, knowing accented letters become "% some string %". Hat's off.