• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

declination of all words beginning with all consonants/vowels

Tetonne

New Member
hello to all,
I hope you and your family are doing well.

I'm looking for a vba solution or a formula that would allow me to do the following:
for a given word in A1
- if the 2nd letter of this word is a vowel, then replace the 1st letter of the word by all the consonants,
otherwise replace the 1st letter of the word by all the vowels
e.g:
PARIS would give BARIS; CARIS; DARIS…ZARIS
ARBRE would give EBRE;IBRE;OBRE;UBRE,YBRE
no matter if the word exist or not
can someone help?
 
Last edited:

Peter Bartholomew

Well-Known Member
I didn't read your question properly so did not recognise that you wanted a VBA solution. I use Excel 365 so the LET functions allows me to program in the cell using worksheet formulae. For what it is worth
Code:
= LET(
  char2, MID(word, 2, 1),
  isVowel?, XLOOKUP( char2, vowel, ISTEXT(vowel), FALSE),
  char1, IF(isVowel?, consonant, vowel),
  REPLACE(word,1,1,char1) )
The workbook names 'consonant' and 'vowel' refer to constant arrays
={"B";"C";"D";"F";"G";"H";"J";"K";"L";"M";"N";"P";"Q";"R";"S";"T";"U";"V";"X";"Z"}
={"A";"E";"I";"O";"U";"Y"}
 
Please try at B2:Bxx
=IFERROR(REPLACE(A$1,1,1,CHAR(AGGREGATE(15,6,ROW(A$65:A$90)/(ABS(ISNA(MATCH(CODE(A$1),{65;69;73;79;85;89},))-ISNUMBER(MATCH(ROW(A$65:A$90),{65;69;73;79;85;89},)))-(ROW(A$65:A$90)=CODE(A$1))),ROWS(B$1:B1)))),"")

or MS365
=LET(w,A1,v,{65;69;73;79;85;89},s,SEQUENCE(26,,65),REPLACE(w,1,1,CHAR(FILTER(s,ABS(ISNA(MATCH(CODE(w),v,))-ISNUMBER(MATCH(s,v,)))-(s=CODE(w))))))
 

Attachments

Top