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


---
Message posted from http://www.ExcelForum.com/

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.

--

Vasant


"rjtaylor " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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 <vasantn *AT* aol *DOT* com wrote in message
...
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.

--

Vasant


"rjtaylor " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

"Tom Ogilvy" wrote...
....
Function retText(var)
retText = ""
If VarType(retText) = 8 Then
....

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



--
Regards,
Tom Ogilvy



Harlan Grove wrote in message
...
"Tom Ogilvy" wrote...
...
Function retText(var)
retText = ""
If VarType(retText) = 8 Then
...

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?

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:

--
Message posted from http://www.ExcelForum.com