LinkBack URL
About LinkBacks
ADEEILN
ADEEILN
ADEILNU
ADEILNU
ADIILNO
DEEILNO
DEILNOO
DEILNOU
I have this on a1
need results as follows on B and C
ADEEILN AEE I
ADEEILN AEE I
ADEILNU AEI U
ADEILNU AEI U
ADIILNO AII O
DEEILNO EEI O
DEILNOO EII O
DEILNOU EIO U
ADEEILN
ADEEILN
ADEILNU
ADEILNU
ADIILNO
DEEILNO
DEILNOO
DEILNOU
I have this on a1
need results as follows on B and C
ADEEILN AEE I
ADEEILN AEE I
ADEILNU AEI U
ADEILNU AEI U
ADIILNO AII O
DEEILNO EEI O
DEILNOO EII O
DEILNOU EIO U
b formula logic , pull 3 vowels in alpha , c formula , find the last vowel one not on B
Why one vowel in C: please DO explain your logic.
And wasn't there a macro posted recently that did something similar i.e. checking 3 consecutive vowels ?
Are the strings in column A always exactly 7 letters?
In all your examples, the vowels in the original string are already in alpha order. Will that always be true?
In your example results, why is there a space before the last vowel?
Adapting shg's function:
in B1
Please Login or Register to view this content.
=LEFT(mm(A1),3)
in B1
=RIGHT(mm(A1))
Try this
Enter in B1 and copy down
Formula:
Please Login or Register to view this content.
Enter in C1 and copy down
Formula:Please Login or Register to view this content.
v A B C 1 ADEEILN AEE I 2 ADEEILN AEE I 3 ADEILNU AEI U 4 ADEILNU AEI U 5 ADIILNO AII O 6 DEEILNO EEI O 7 DEILNOO EIO O 8 DEILNOU EIO U
Last edited by AlKey; 02-21-2017 at 09:08 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thank you ALKEY , works , Macro of John Topley will use too
Macro is though stronger since I don't have to edit formula different pattern of words not necessarily all will be DLN
I did not find necessary to start new thread on this , I need to pull vowels by formula
I am using macro this time , need formula , The need of formula saves time since ,
copy paste is taking long , from one program to another
AABDERR
AADEPRR
AADERRS
AADERRW
AADERRY
AADINRR
AADIRRW
AADLRRU
AADOPRR
ABDEERR
I have this ,
Expected results on B1 downwards data on A1
AABDERR AAE
AADEPRR AAE
AADERRS AAE
AADERRW AAE
AADERRY AAE
AADINRR AAI
AADIRRW AAI
AADLRRU AAU
AADOPRR AAO
ABDEERR AEE
Last edited by makinmomb; 02-27-2017 at 05:42 AM.
Will only macro do this , formula looks tough type ?
You can use this formula in B1, assuming your data starts in A1:
=IF(ISNUMBER(SEARCH(MID(A1,1,1),"AEIOU")),MID(A1,1,1),"")&IF(ISNUMBER(SEARCH(MID(A1,2,1),"AEIOU")),MID(A1,2,1),"")&IF(ISNUMBER(SEARCH(MID(A1,3,1),"AEIOU")),MID(A1,3,1),"")&IF(ISNUMBER(SEARCH(MID(A1,4,1),"AEIOU")),MID(A1,4,1),"")&IF(ISNUMBER(SEARCH(MID(A1,5,1),"AEIOU")),MID(A1,5,1),"")&IF(ISNUMBER(SEARCH(MID(A1,6,1),"AEIOU")),MID(A1,6,1),"")&IF(ISNUMBER(SEARCH(MID(A1,7,1),"AEIOU")),MID(A1,7,1),"")
It might be easier to understand what is happening if I split the formula manually:
=IF(ISNUMBER(SEARCH(MID(A1,1,1),"AEIOU")),MID(A1,1,1),"")
&IF(ISNUMBER(SEARCH(MID(A1,2,1),"AEIOU")),MID(A1,2,1),"")
&IF(ISNUMBER(SEARCH(MID(A1,3,1),"AEIOU")),MID(A1,3,1),"")
&IF(ISNUMBER(SEARCH(MID(A1,4,1),"AEIOU")),MID(A1,4,1),"")
&IF(ISNUMBER(SEARCH(MID(A1,5,1),"AEIOU")),MID(A1,5,1),"")
&IF(ISNUMBER(SEARCH(MID(A1,6,1),"AEIOU")),MID(A1,6,1),"")
&IF(ISNUMBER(SEARCH(MID(A1,7,1),"AEIOU")),MID(A1,7,1),"")
It just looks at each letter in turn to see if it is a vowel, and if so it adds it to a composite string.
Hope this helps.
Pete
Why not simple put all these very similar requests in one workbook OR put the the macros in PERSONAL.XLSB where they are available to any workbook.
It is much easier to remove consonants than searching.
Formula:Please Login or Register to view this content.
v A B 1 AABDERR AAE 2 AADEPRR AAE 3 AADERRS AAE 4 AADERRW AAE 5 AADERRY AAE 6 AADINRR AAI 7 AADIRRW AAI 8 AADLRRU AAU 9 AADOPRR AAO 10 ABDEERR AEE
Thank you all
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks