Easy question - help !!!

[jal]

I'm using vbscript which is almost identical to VBA. I did the hard part, I read in a textfile, parsed it, and inserted the words and numbers into Excel.

But it's the "easy" part that's killing me. One of the columns which I am parsing has spaces prepended, such as

" John Smith"

I can't get rid the spaces. I've tried

Name = RTrim(Name)
name = Ltrim(Name)
name = Trim(Name)

I've even tried looping through the chars

str = ""
For i = 1 to Len(word)
C = Mid(word, i,1)
if C <> " " Then str = str & C
next

it's not working. They "look" like spaces but vb won't recognize them as spaces and so won't trim them. Why?

I'm trying to get this project on the floor NOW. Help !!!

 

[jal]

Well, I found an ugly solution, it's better than nothing:

function IsLetter(C)
IsLetter = False
if C = "." Then
isLetter = True
Exit Function
End if
Dim Arr(26)
Arr(0) = "a"
Arr(1) = "b"
Arr(2) = "c"
Arr(3) = "d"
Arr(4) = "e"
Arr(5) = "f"
Arr(6) = "g"
Arr(7) = "h"
Arr(8) = "i"
Arr(9) = "j"
Arr(10) = "k"
Arr(11) = "l"
Arr(12) = "m"
Arr(13) = "n"
Arr(14) = "o"
Arr(15) = "p"
Arr(16) = "q"
Arr(17) = "r"
Arr(18) = "s"
Arr(19) = "t"
Arr(20) = "u"
Arr(21) = "v"
Arr(22) = "w"
Arr(23) = "x"
Arr(24) = "y"
Arr(25) = "z"
dim i
For i =0 to 25
if (C = arr(i)) or C = UCase(arr(i)) Then
IsLetter = True
Exit function
end if
Next
end function

 

[georgedwilkinson]

You could have just checked if the character was in the "letter" ascii range. No need for an array that way, just a simple loop.

 

[Banana]

I'm not sure what other characters could look like space, but since you have non-space character as well space character, it may be preferable to do a Select Case:

Public Function RemoveNonLetters(sInput As String)

Dim i As Long
Dim iLength As Long
Dim sOutput As String
Dim sLetter As String

iLength = Len(Input)

For i = 1 to iLength
   sLetter = Mid(sInput,i)
   Select Case Asc(sLetter)
      Case 65 to 90, 97 to 122
          sOutput = sOutPut & sLetter
   End Select
Next

RemoveNonLetters = sOutput

End Function

HTH

 

[jal]

Great code as always. Thanks !

 

[jal]

You could have just checked if the character was in the "letter" ascii range. No need for an array that way, just a simple loop.

Oh, I didn't see your comment, I only saw Banana's.

Thanks !!!