MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Remove the spaces between characters in VBA

  Asked By: Ella    Date: Oct 05    Category: MS Office    Views: 11062

I have the tab names like " Year1" and "Year 1". I need to use some
statements like

if trim(pagename)="Year1" then xxx

The problem is that I wanna remove the space between "Year" and "1" but
functions like "clean" doesn't work(it is an excel function). Is there
any functions in VBA to remove innder spaces?



7 Answers Found

Answer #1    Answered By: Fadwa Ahmed     Answered On: Oct 05


s = Replace(s,   ,   )

Answer #2    Answered By: Bin Fischer     Answered On: Oct 05

Try this. Found it on the web (posted by an unknown user). I have used it and
it works great.

Remove Spaces
This is one of those functions  that, having built it, I wondered how I had ever
managed without! It came about because I wanted to build a list of email
addresses from a list of people's names. It was a big list! The plan was simple
enough... the email address would be firstname-dot-lastname-@-domainname.

Answer #3    Answered By: Rudy Turner     Answered On: Oct 05

This is very fast....

Function fncStripJoin(spS As String) As String

Dim slS() As String

slS = Split(spS," ")
fncStripJoin = Join(slS, "")
' ***********************************************************************
End Function

I also use a "generic" procedure which will strip any single or set of
characters including control characters.

Function fncStripChrs(strpString As String)
' Strip some characters.

Dim intlM As Integer
Dim strlS As String

strlS = ""
For intlM = 1 To Len(strpString)
Select Case Asc(Mid(strpString, intlM, 1))
Case 13, 7, 10, 9, 150, 147 ' List of Ascii codes for stripping.
Case Else
strlS = strlS & Mid(strpString, intlM, 1)
End Select
fncStripChrs = strlS
' ***********************************************************************
End Function

... And finally this one.

Function fncAllTrim(spS As String) _
As String
' Trim all spaces  from a string.

Dim slS As String
Dim ilChr As Integer
Dim slChr As String * 1

slS = ""
For ilChr = 1 To Len(spS)
slChr = Mid(spS, ilChr, 1)
If slChr <> " " Then
slS = slS & slChr
End If
Next ilChr
fncAllTrim = slS
' ***********************************************************************
End Function

I did have a function  where you passed a list of chrs to zap but I couldn't
find it... Sorry.

Answer #4    Answered By: Zivah Levi     Answered On: Oct 05

I like the split/join, and my last email was messed up. I think the simplest

s = Replace(s, " ", "")

Answer #5    Answered By: Shayne Anderson     Answered On: Oct 05

I really just threw it in for novelty... but it is fast. I like
SPLIT. I use it a lot in parsing and so on. Replace is fast too. I like the
REPLACE as well but I'd prob put it in a 1 to 5 FOR.. NEXT to get rid of
doubles spaces. The SPLIT/JOIN suffers the same prob.

But I think you're right ... The REPLACE is probably the simplest.

The loop through for ASCII codes is the best I think. In the one where I
passed a string I did an INSTR on the passed list for the ascii code of the
character. It meant I could pass an OPTIONAL string of ASCII codes like

I had a big problem  once with "smart" quotes, and that's what I used that

Answer #6    Answered By: Ludano Ricci     Answered On: Oct 05

Both Split/Join and Replace both seem to handle the issue of multiple space
without difficulty. Here is some really bad test code that displays the
desired results:

Sub ab()

Dim s As String

Dim a As Variant

Dim b As String

s = "Hello folks, how y'all doing today???"

a = Split(s, " ")

b = Join(a, "")

MsgBox b ' the split/join method

MsgBox Replace(s, " ", "") ' the Replace method

End Sub

I like your function  that has all that added functionality -- could come in
handy someday.

Answer #7    Answered By: Shannon Freeman     Answered On: Oct 05

Ok.... I admit I "thought" it would give erroneous results for multiple
spaces. Glad to see I wuz wrong!!!

Yupper... As I said, I use it for parsing a lot so it's great for running
through each of the SPLIT array items and testing for stuff.

At the mo I'm working on a routine for sorting Dims and getting rid of the
ones not referenced. SPLIT works well here... I SPLIT each line and sort the
results on the 1st dimension (Nothing to do with little green women... My
surname being Green.. ), arrays normally being zero based.

Didn't find what you were looking for? Find more on Remove the spaces between characters in VBA Or get search suggestion and latest updates.