Best way to remove a character from a string 2

[venkman]

What's the best way to remove all occurrences of a single character from a string?

right now I'm using this technique:

Public Function remChar(s As String, c As String) As String
Dim varr As Variant
varr = Split(s, c)
remChar = Join(varr, "&quot
End Function


where s is the original string and c is the character I'm trying to remove.

This works fine, but I was wondering about more elegant solution. Any suggestions?

-Venkman

 

[Zathras]

Actually, I think that's pretty darn elegant.

However, since I can't seem to find Split and Join in my copy of Excel 97, I have to do it this way (which is much less elegant than what you are doing):
[blue]

Public Function remChar(s As String, c As String) As String
Dim i As Integer
  remChar = s
  i = InStr(remChar, c)
  While i > 0
    remChar = Left(remChar, i - 1) + Mid(remChar, i + 1)
    i = InStr(remChar, c)
  Wend
End Function

[/color]

 

[Stretchwickster]

Just for your info Zathras - I recently found out that Split (and probably Join) is/are a new addition to 2000 which was a pain in the backside - cos I developed a workbook in 2000 then tested it in 97 and had to make loads of changes and use custom functions!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[TonyJollans]

Hi venkman,

Split and Join are new in 2K. Also new is Replace, and ..

s = Replace(s, c, "")

.. should do the trick a bit more efficiently.

Enjoy,
Tony

 

[venkman]

Thanks Tony, I figured there was a one command way to do it. Exactly what I was looking for.

-Venkman

 

[Yogi39]

Trying to remove vowels from a sting and replace them with an X.

 

[VBAjedi]

That's nice, Yogi, but it looks like you removed a consonant, not a vowel, from "string"

[ROFL]

Try:

MyString = "Test String"
For x = 1 To Len(MyString)
   a = Mid(MyString, x, 1)
   Select Case UCase(a)
   Case "A", "E", "I", "O", "U"
      MyString = Left(MyString, x - 1) & "X" & Right(MyString, (Len(MyString) - x))
   Case Else
      ' Not a vowel, do nothing
   End Select
Next x
MsgBox MyString

Let me know if that helps!

VBAjedi

 

[Yogi39]

Thanks for the help works like a charm !