Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
How to remove vowels from words? Fpr example, in one row we have
google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Maybe this function can help you:
'------------------------------------------------------ Function removeVowel(VowelWord As String) As String Dim NoVowelWord As String For L = Len(VowelWord) To 1 Step -1 If Mid(VowelWord, L, 1) = "a" Or _ Mid(VowelWord, L, 1) = "e" Or _ Mid(VowelWord, L, 1) = "i" Or _ Mid(VowelWord, L, 1) = "o" Or _ Mid(VowelWord, L, 1) = "u" Then Else NoVowelWord = Mid(VowelWord, L, 1) & NoVowelWord End If Next L removeVowel = NoVowelWord End Function '------------------------------------------------------ It returns the word without vowels Cheers Carlo On Dec 19, 4:35 pm, wrote: How to remove vowels from words? Fpr example, in one row we have google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On 19 Dec, 07:43, carlo wrote:
Maybe this function can help you: '------------------------------------------------------ Function removeVowel(VowelWord As String) As String Dim NoVowelWord As String For L = Len(VowelWord) To 1 Step -1 If Mid(VowelWord, L, 1) = "a" Or _ Mid(VowelWord, L, 1) = "e" Or _ Mid(VowelWord, L, 1) = "i" Or _ Mid(VowelWord, L, 1) = "o" Or _ Mid(VowelWord, L, 1) = "u" Then Else NoVowelWord = Mid(VowelWord, L, 1) & NoVowelWord End If Next L removeVowel = NoVowelWord End Function '------------------------------------------------------ It returns the word without vowels Cheers Carlo On Dec 19, 4:35 pm, wrote: How to remove vowels from words? Fpr example, in one row we have google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. Why is the loop stepping backwards? |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
If he didn't go backwards, that code would reverse the text, so you would
get __rcm ,llx ,lgg__ instead of __ggl, xcll, mcr__ -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Duncan" wrote in message ... On 19 Dec, 07:43, carlo wrote: Maybe this function can help you: '------------------------------------------------------ Function removeVowel(VowelWord As String) As String Dim NoVowelWord As String For L = Len(VowelWord) To 1 Step -1 If Mid(VowelWord, L, 1) = "a" Or _ Mid(VowelWord, L, 1) = "e" Or _ Mid(VowelWord, L, 1) = "i" Or _ Mid(VowelWord, L, 1) = "o" Or _ Mid(VowelWord, L, 1) = "u" Then Else NoVowelWord = Mid(VowelWord, L, 1) & NoVowelWord End If Next L removeVowel = NoVowelWord End Function '------------------------------------------------------ It returns the word without vowels Cheers Carlo On Dec 19, 4:35 pm, wrote: How to remove vowels from words? Fpr example, in one row we have google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. Why is the loop stepping backwards? |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On 19 Dec, 09:42, "Bob Phillips" wrote:
If he didn't go backwards, that code would reverse the text, so you would get __rcm ,llx ,lgg__ instead of __ggl, xcll, mcr__ -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Duncan" wrote in message ... On 19 Dec, 07:43, carlo wrote: Maybe this function can help you: '------------------------------------------------------ Function removeVowel(VowelWord As String) As String Dim NoVowelWord As String For L = Len(VowelWord) To 1 Step -1 If Mid(VowelWord, L, 1) = "a" Or _ Mid(VowelWord, L, 1) = "e" Or _ Mid(VowelWord, L, 1) = "i" Or _ Mid(VowelWord, L, 1) = "o" Or _ Mid(VowelWord, L, 1) = "u" Then Else NoVowelWord = Mid(VowelWord, L, 1) & NoVowelWord End If Next L removeVowel = NoVowelWord End Function '------------------------------------------------------ It returns the word without vowels Cheers Carlo On Dec 19, 4:35 pm, wrote: How to remove vowels from words? Fpr example, in one row we have google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. Why is the loop stepping backwards? Only because he is appending to the output string backwards too. Just seems a bit odd. |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
no VBA
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(A1,"a",""),"e",""),"I",""),"o",""),"u","") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... How to remove vowels from words? Fpr example, in one row we have google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Just to add to Bob's formula.
=substitute() is case sensitive (and I bet autocorrect changed his i to I <bg). Another alternative: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(LOWER(A1), "a",""),"e",""),"i",""),"o",""),"u","") Bob Phillips wrote: no VBA =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(A1,"a",""),"e",""),"I",""),"o",""),"u","") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... How to remove vowels from words? Fpr example, in one row we have google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. -- Dave Peterson |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi Guys,
just to add a comment to my code in regards to backstepping. I first had a total different approach, which was total crap, that's where the backward steps are coming from. Obviously it works as well if you go the other way around, didn't realize that though until I saw your post :) Here's the forward version, although the other one should work fine: '------------------------------------------------------ Function removeVowel(VowelWord As String) As String Dim NoVowelWord As String For L = 1 to Len(VowelWord) If Mid(VowelWord, L, 1) = "a" Or _ Mid(VowelWord, L, 1) = "e" Or _ Mid(VowelWord, L, 1) = "i" Or _ Mid(VowelWord, L, 1) = "o" Or _ Mid(VowelWord, L, 1) = "u" Then Else NoVowelWord = NoVowelWord & Mid(VowelWord, L, 1) End If Next L removeVowel = NoVowelWord End Function '------------------------------------------------------ cheers carlo On Dec 20, 12:36 am, Dave Peterson wrote: Just to add to Bob's formula. =substitute() is case sensitive (and I bet autocorrect changed his i to I <bg). Another alternative: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(LOWER(A1), "a",""),"e",""),"i",""),"o",""),"u","") Bob Phillips wrote: no VBA =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(A1,"a",""),"e",""),-"I",""),"o",""),"u","") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... How to remove vowels from words? Fpr example, in one row we have google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. -- Dave Peterson- Hide quoted text - - Show quoted text - |
#9
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On 20 Dec, 00:29, carlo wrote:
Hi Guys, just to add a comment to my code in regards to backstepping. I first had a total different approach, which was total crap, that's where the backward steps are coming from. Obviously it works as well if you go the other way around, didn't realize that though until I saw your post :) Here's the forward version, although the other one should work fine: '------------------------------------------------------ Function removeVowel(VowelWord As String) As String Dim NoVowelWord As String For L = 1 to Len(VowelWord) If Mid(VowelWord, L, 1) = "a" Or _ Mid(VowelWord, L, 1) = "e" Or _ Mid(VowelWord, L, 1) = "i" Or _ Mid(VowelWord, L, 1) = "o" Or _ Mid(VowelWord, L, 1) = "u" Then Else NoVowelWord = NoVowelWord & Mid(VowelWord, L, 1) End If Next L removeVowel = NoVowelWord End Function '------------------------------------------------------ cheers carlo On Dec 20, 12:36 am, Dave Peterson wrote: Just to add to Bob's formula. =substitute() is case sensitive (and I bet autocorrect changed his i to I <bg). Another alternative: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(LOWER(A1), "a",""),"e",""),"i",""),"o",""),"u","") Bob Phillips wrote: no VBA =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(A1,"a",""),"e",""),-"I",""),"o",""),"u","") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... How to remove vowels from words? Fpr example, in one row we have google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. -- Dave Peterson- Hide quoted text - - Show quoted text - This is fun, just seeing everyone's personal approach to this macro,. Mine would be; Public Function novowels(ByVal txt As String) If txt = "" Then Exit Function For l = 1 To Len(txt) Select Case LCase(Mid(txt, l, 1)) Case "a", "e", "i", "o", "u" Case Else novowels = novowels & Mid(txt, l, 1) End Select Next l End Function |
#10
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
There was no i in the example <g
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... Just to add to Bob's formula. =substitute() is case sensitive (and I bet autocorrect changed his i to I <bg). Another alternative: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(LOWER(A1), "a",""),"e",""),"i",""),"o",""),"u","") Bob Phillips wrote: no VBA =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(A1,"a",""),"e",""),"I",""),"o",""),"u","") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... How to remove vowels from words? Fpr example, in one row we have google, excell, macro,... and the result in another row is ggl, xcll, mcr,... Thanks. -- Dave Peterson |
#11
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
That sounds like a sign in every lockerroom I've ever seen:
There is no I in TEAM. (But there is a ME, but it's out of order <vbg.) Bob Phillips wrote: There was no i in the example <g -- HTH Bob <<snipped |
#12
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
There's also MEAT in it....but that's a whole different story :D
On Dec 20, 11:48*pm, Dave Peterson wrote: That sounds like a sign in every lockerroom I've ever seen: There is no I in TEAM. (But there is a ME, but it's out of order <vbg.) Bob Phillips wrote: There was no i in the example <g -- HTH Bob <<snipped |
#13
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() |
#14
![]() |
|||
|
|||
![]()
That's it! You should now have a new row or column with the words from the original column, but with all vowels removed.
__________________
I am not human. I am an Excel Wizard Last edited by kevin : April 4th 23 at 10:07 AM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to do those lines on top of vowels? | Excel Discussion (Misc queries) | |||
removing a space between words in a cell | Excel Worksheet Functions | |||
removing cells with certain words in it | Excel Programming | |||
removing words/phrase from sentences | Excel Programming | |||
Removing rows featuring certain words | Excel Programming |