Pulling Consonants and Vowels

[makinmomb]

I have this on A1 and B1

NOTAIRE AEINORT
OTARINE AEINORT
ETAERIO AEEIORT
ANEROID ADEINOR
AILERON AEILNOR
ALERION AEILNOR

I need formula on b1 and c1 to do the following , formula to point B

AEINORT NRT AEIO
AEINORT NRT AEIO
AEEIORT RT AEEIO
ADEINOR DNR AEIO
AEILNOR LNR AEIO
AEILNOR LNR AEIO

Pulling consonants and vowels in the order of how the ALPHA GRAM of the word is

 

[makinmomb]

Formula on c1 and d1 , I meant

 

[mikerickson]

You could use this UDF, with the formulas

=ExtractString(B1,"aeiou")
=ExtractString(B1,"!aeiou")

Function ExtractString(aString As String, extractWhat As String, _
                    Optional CaseSensitive As Boolean = False, Optional AlphaOnly As Boolean = True) As String
    Dim testString As String, testChr As String
    Dim i As Long
    testString = aString
    
    If Not CaseSensitive Then
        testString = LCase(testString)
        extractWhat = LCase(extractWhat)
    End If
    
    If Not extractWhat Like "[?*]" Then
        extractWhat = "[" & extractWhat & "]"
    ElseIf extractWhat = vbNullString Then
        extractWhat = "*"
    End If
    
    For i = 1 To Len(testString)
        testChr = Mid(testString, i, 1)
        If testChr Like extractWhat Then
            If AlphaOnly And (UCase(testChr) = LCase(testChr)) Then
            Else
                ExtractString = ExtractString & Mid(aString, i, 1)
            End If
        End If
    Next i

End Function

 

[Rick Rothstein]

A formula for column "C" (constants) is relatively simple...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"A",""),"E",""),"I",""),"O",""),"U","")

but to do the same thing for vowels would require 20 nested SUBSTITUTE function calls. You can do that if you want, but personally, I would create two UDFs (user defined functions) and use them instead. So, once you install them as described below, then you can use them just like any other built-in Excel function. So, you would put these formulas in the indicated cells and then just copy them down to the end of your data...

C1: =Consonats(B1)

D1: =Vowels(B1)

Okay, here is the code for these functions...

Function Vowels(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!AEIOU]" Then Mid(S, X) = " "
  Next
  Vowels = Replace(S, " ", "")
End Function

Function Consonants(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[AEIOU]" Then Mid(S, X) = " "
  Next
  Consonants = Replace(S, " ", "")
End Function

Note: These functions assume the text they are working one are all upper case letters as your example showed.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Vowels and Consonants just like they were built-in Excel functions as described above.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[makinmomb]

Thanks , mikerickson , cant run the macro , since i am not macro literate ,
does the macro need additional lines for it to work

Wow so curious

 

[mikerickson]

If you put that code in a normal module, it will add a new function ExtractString, that is used in cell formulas like the other Excel function , like SUM.

 

[makinmomb]

Thanks Rick and Miker

Awesome and beautiful

Thank you again

 

[makinmomb]

Looks like it is word length compatible will even work with letters that are 8 letters likely ?

 

[Rick Rothstein]

Looks like it is word length compatible will even work with letters that are 8 letters likely ?

Both of our solutions adapt themselves automatically to the length of the text they are given to work on.