What formula can I use to substitute the final vowel of a text string with another character?
For example, the formula should give the following results (if # is the character I want to replace the final vowel)
orange > orang#
maroon > maro#n
red > r#d
green > gre#n
pink > p#nk
brown > br#wn
Last edited by Henry54289; 12-27-2020 at 01:09 PM.
VBA does not have an alpha character library sophisticated enough to check for alphabet vowels in a string. thus, you will have to search strings one char by one char, backwards, and check for the first vowel. for instance, if you want to replace the last vowel of 5 strings with the letter ''q'', this would be 1 way:
wow, now that I realize it, I have written something very long. sorry about that! hope it works. it obviously can be greatly reduced.
Please Login or Register to view this content.
A user-defined function thus:Attachment 710556Please Login or Register to view this content.
Click on the link above to see pic of how to use the udf.
If you have 365 with the dynamic array functions
=IFERROR(REPLACE(A2,LEN(A2)-AGGREGATE(15,6,SEARCH({"a","e","i","o","u"},CONCAT(MID(A2,SEQUENCE(,LEN(A2),LEN(A2),-1),1))),1)+1,1,"#"),A2)
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. As you are new, I will provide the link fir you this time: https://www.reddit.com/r/excel/comme...f_text_string/.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
** Oh, just realized someone already with same method!!! SORRY!!
Your profile says, MS Ver. 2020, not sure what it is, so I assumed it is MS365, the try this,
Q06.pngPlease Login or Register to view this content.
Last edited by phatdear; 12-28-2020 at 02:14 AM. Reason: Just realized someone already answered with same method.
Please try
=REPLACE(A2,AGGREGATE(14,6,SEARCH({"a","e","i","o","u"},A2,LEN(A2)-{1;2;3;4;5}),1),1,"#")
or add more number, but 5 should be enough for normal word
=REPLACE(A2,AGGREGATE(14,6,SEARCH({"a","e","i","o","u"},A2,LEN(A2)-{1;2;3;4;5;6;7;8;9;10}),1),1,"#")
Last edited by Bo_Ry; 12-28-2020 at 02:35 AM.
Another approach, in order to be different.
Please Login or Register to view this content.
Pl see file.
In B2 then copy down.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks