Extract Letters from the Word

[deciog]

Sirs, Good Morning.

I need to extract all letters contained in the auxiliary cells of the word in cell C3

If you can do without ancillary column much better

Excel 2016 version only non-VBA formulas

Thank you very much in advance

Decio

 

[Chihiro]

Just use nested substitute.

 

[deciog]

Chihiro

Thanks for answering

I would like to replace all the letters of the columns, in a single replacement
It is possible?

Decio

 

[Chihiro]

AFAIK, not without UDF (VBA), or some other tool such as PowerQuery.

As SUBSTITUTE function only replaces one (first word/letter etc), even if you pass array.

 

[deciog]

I am doing so, but there is a mistake painted in red.

Is there another solution?

Decio

 

[Chihiro]

Like I said, use Substitute. It will only replace exact match.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,E3,""),F3,""),G3,""),H3,""),I3,""),J3,""),K3,""),L3,""),M3,"")

Though personally, I don't use Excel for most text manipulation needs.

 

[deciog]

Chihiro

Many thanks for helping.

Yes, this way works, I have this, but, have another way to do?

Decio

 

[Peter Bartholomew]

I don't see any mistake.
1. Word; A relative reference to the word you wish to edit
2. n: = LEN(Word); the number of characters contained the Word
3. aRow; a relative reference to the entire row containing the active cell
4. k: = COLUMN(INDEX(aRow,1) : INDEX(aRow,n))
Note; for me this is simply =SEQUENCE(1,n)
5. chr: = MID( word, k, 1 ); the characters as an array
6. selected: = IF( COUNTIFS(aux, chr ), "", chr ); array with unwanted characters extracted
7. = CONCAT(selected); Worksheet formula giving result

 

[deciog]

Peter, Good Morning.

I liked the way it did
But note that there is an error in the word "Armazem" it brings "mzm" and the correct one would be "Amzm"

Let's do it another way.
Replace all even word positions with hyphen "-"

Example:
"Armazem" result "A-m-z-m" or
"Contabilidade" result "C-n-a-i-i-a-e"

It is possible
A challenge for all

Thank you
Decio

 

[navic]

Try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,E3,"-"),F3,"-"),G3,"-"),H3,"-"),I3,"-"),J3,"-"),K3,"-"),L3,"-"),M3,"-")

 

[deciog]

navic

Thanks for replying, this way I already do but would like another approach of formula

Thank you

Decio

 

[navic]

Perhaps

=TRIM(MID(C3,1,1)&MID(C3,3,1)&MID(C3,5,1)&MID(C3,7,1)&MID(C3,9,1)&MID(C3,11,1)&MID(C3,13,1)&MID(C3,15,1)&MID(C3,17,1)&MID(C3,9,1)&MID(C3,19,1)&MID(C3,21,1)&MID(C3,23,1))

 

[Peter Bartholomew]

Hello Decio

It depend on one's understanding of your requirements. I took it that the initial A of "Armazem" should be eliminated because "a" appears within an auxiliary cell. If it is alternate characters you are looking for, you have already provided an answer in your auxiliary table. My approach (modified from previous) that achieves your desired result has
1. Word; As before
2. n: = CEILING( LEN(word), 2 ) / 2; the number of characters to be returned from word
3. aRow; as before
4. k: = 2 * COLUMN(INDEX(aRow,1) : INDEX(aRow,n)) - 1; sequence of alternate numbers
Note; for me this is simply =SEQUENCE(1,n,1,2)
5. chr: = MID( word, k, 1 ); the characters as an array
6. = CONCAT(chr); Worksheet formula giving result

 

[deciog]

Peter, Good Morning.

Sorry for the delay yesterday I had to have lunch with the family.

Fantastic
It was great, that's the way it is.
Thank you very much.

Hugs

Decio

 

[Peter Bartholomew]

No need to apologize!
I hope lunch with the family was great.

 

[deciog]

Peter

Yes, it was great, and thanks for the help.

Hugs

Decio