consonant pull by ALPHA

[makinmomb]

APADANA
ATALAYA
ARAWANA
ARAARAS

Have the following on A1

Need formula on B1

results required

DNP
LTY
NRW
RRS

[makinmomb]

Vowels will change for words like AMADODA

[alansidman]

With Power Query

Please Login or Register  to view this content.

[makinmomb]

I need DNP not PDN on the first result

[AliGW]

Is there any chance you could change the tone of your responses? It's all very 'demanding' at the moment - I realise that English may not be your first language, but 'please' and 'thank you' are universal courtesies.

[alansidman]

I suggest that you take the data from PQ as shown and close and load to Excel. Then do a Text to Columns and then do a horizontal alpha sort. Once this is complete, concatenate the columns.

[XLent]

Based on your examples, you could use a UDF along lines of below:

Please Login or Register  to view this content.

the above, stored in standard module, could be called along lines of: =ConsonantSort(A1) ... you have option of calling with modified exclusion criteria also, e.g. =ConsonantSort(A1,"EIOU") would return all the As.

[Metoo7]

The below formula only applicable for Excel 2013 and above. If you are using an older version, you have to consider Power Query or VBA.

Please Login or Register  to view this content.

You may also use this formula if you have Office 365.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Thia ARRAY formula works.
Max characters is assumed as 30.
Change it if required more.
In B2 copied down

Please Login or Register  to view this content.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[Metoo7]

eliminate all vowel letters and sort by alphabet.

Please Login or Register  to view this content.

[janmorris]

step 1, remove the vowels (option 1):

with all words in column A, to remove all vowels, place this formula into cell B1:

Please Login or Register  to view this content.

then drag down to other cells

step 1, remove the vowels (option 2):
you can create a VBA Module with this code:

Please Login or Register  to view this content.

then use this formula in cell B1:

Please Login or Register  to view this content.

then drag down to other cells


step 2, sort the consonants (option 1, for exactly 3 consonants):
if there will only be 3 consonants, to sort them (AFTER the vowels have been removed), place this formula into cell C1:

Please Login or Register  to view this content.

then drag down to other cells

step 2, sort the consonants (option 2, for any number of consonants):
if there will be more OR less than 3 consonants, to sort them (AFTER the vowels have been removed), you can create a VBA Module with this code:

Please Login or Register  to view this content.

then use this formula in cell C1:

Please Login or Register  to view this content.

then drag down to other cells

[Rick Rothstein]

Here is a shorter array-entered** formula that should work (provided your version of Excel has the TEXTJOIN function in it)...

Please Login or Register  to view this content.

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

[janmorris]

you could try to place this formula in cell B1:

Please Login or Register  to view this content.

then drag down to other cells

[Rick Rothstein]

Please Login or Register  to view this content.

Actually, if your version of Excel has the CONCAT function, we can shorten the above array-entered** formula by 6 characters...

Please Login or Register  to view this content.

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself