left, mid, right function question 2

[shannanl]

I am using the following to seperate words from a string. Example of the string would be "John Q Doe".

(Left(strName, InStr(strName, " &quot - 1)) returns "John"
(Mid(strName, InStr(strName, " &quot + 1)) returns "Q Doe"

How can I return only the "Q" without the "Doe"? In other words anything between the blank spaces?

Thanks in advance for your help.

Shannan

 

[DrJavaJoe]

if Len(trim(Left((Mid(strName, InStr(strName, " &quot + 1)),2))) = 1 then 'test for mid initial
debug.print Left((Mid(strName, InStr(strName, " &quot + 1)),1)
else
debug.print (Mid(strName, InStr(strName, " &quot + 1))
end if

 

[DrJavaJoe]

I suppose you would need to modify to account for "John Q. Doe" or maybe names like O Conner without the "'". Also prefixes such as "Mr and Mrs John Q Doe". With this in mind Strongm should be along shortly and suggest that this may be a good place to use RegExp.

 

[michaelcoleman]

dude,

Try the split command.

If the format is first_name middle_name last_name
do this:

dim strSplit() as string
dim strStuff as string

strStuff = "John Q Public"
strsplit() = split(strstuff," &quot
strsplit(0) = firstname
strsplit(1) = middlename
strsplit(2) = lastname

MJC

 

[shannanl]

I tried the split. It worked fine except that split(1) seemed to return the space and split(2) the middle name. That was easy enough to deal with though. I appreciat the help guys.

Thanks,

Shannan