find letter vrs number

  • Thread starter Thread starter rjtaylor
  • Start date Start date
R

rjtaylor

Hi I need to have vba look at a var and determine if the first character
is number or letter and if letter then make a new var from begging to
first space. Can someone help thanks in advance
Rod Taylor
 
Something like:

If Not IsNumeric(Left(var, 1)) Then newvar = Left("b34 s", InStr(1, var, "
") - 1)

This assumes that the variable "var" always contains a space if it begins
with a letter.
 
Some possible enhancements to the basic idea:

Sub Tester5()
Dim s, t, u, v, s1, t1, u1, v1
s = "123ABC": s1 = retText(s)
t = "ABC 456": t1 = retText(t)
u = "12 A 56 B": u1 = retText(u)
v = "abcdefgh": v1 = retText(v)
Debug.Print s, s1
Debug.Print t, t1
Debug.Print u, u1
Debug.Print v, v1
End Sub


Function retText(var)
retText = ""
If VarType(retText) = 8 Then
If Not IsNumeric(Left(var, 1)) Then
retText = Left(var, IIf(InStr(1, var, " ") = 0, _
Len(var), InStr(1, var, " ")))
End If
End If
End Function

Returns
ABC 456 ABC
12 A 56 B
abcdefgh abcdefgh

--
Regards,
Tom Ogilvy



Vasant Nanavati said:
Something like:

If Not IsNumeric(Left(var, 1)) Then newvar = Left("b34 s", InStr(1, var, "
") - 1)

This assumes that the variable "var" always contains a space if it begins
with a letter.
Click to expand...
 
Tom Ogilvy said:
Function retText(var)
retText = ""
If VarType(retText) = 8 Then
Click to expand...
....

If you've just set retText to "", it's guaranteed to be a string, isn't it?
If so, isn't the following If test superfluous?
 
No, it was a typo, thanks for pointing it out:

Function retText(var)
retText = ""
If VarType(var) = 8 Then
If Not IsNumeric(Left(var, 1)) Then
retText = Left(var, IIf(InStr(1, var, " ") = 0, _
Len(var), InStr(1, var, " ")))
End If
End If
End Function
 
I typed var for the word varable not the actual varable I assume tha
var is just the varable I dont understand functions yet

If VarType(var) = 8 Then
why is the 8 there

what does the IIf in
retText = Left(var, IIf(InStr(1, var, " ") = 0, _
mean

sorry I dont get it and thanks
Rod Taylor:
 
Back
Top