Writing a function to remove vowels from a string

[Kavindra]

Hi all,

I have written this tiny snippet. Could you look into as to why it does not return any results

Function removow(Val As String) As String

Dim length As Integer


Dim Concat As String


Dim Cntr As Integer


Cntr = 1


length = (Len(Val))


While Cntr <= length




    If Mid(Val, Cntr, 1) <> "a" And Mid(Val, Cntr, 1) <> "e" And Mid(Val, Cntr, 1) <> "i" And Mid(Val, Cntr, 1) <> "o" And Mid(Val, Cntr, 1) <> "u" Then
    
    Concat = Concat & Mid(Val, Cntr, 1)
    
    End If
    
Cntr = Cntr + 1


Wend


End Function

Thanks a lot guys.

 

[Kavindra]

I hadn't assigned the removow = Concat. It's working now.

It was my first post. :D

See you later.

 

[Norie]

You never set the return value for the function.

Try adding this.

removow  = Concat

 

[Sixthsense]

Or using in built worksheet functions

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"A",""),"E",""),"I",""),"O",""),"U","")

 

[MickG]

You need a linew to return the value to the function , as below:-

Wend
    removow = Concat
End Function

Another option:-

[COLOR="Navy"]Sub[/COLOR] MG03Apr53
[COLOR="Navy"]Dim[/COLOR] n
   [COLOR="Navy"]For[/COLOR] n = 1 To Len([a1])
       [COLOR="Navy"]If[/COLOR] Range("A1").Characters(n, 1).Text Like "[a,e,i,o,u]" [COLOR="Navy"]Then[/COLOR]
          Range("A1").Characters(n, 1).Text = ""
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick