A problem using mid and Instr.

[Gordon]

Hi;

I am trying to extract a substring using a combination of the mid() and
Instr() i.e.

aString = "Jones, Thomas R, Dr."

hold = InStr(1, aString, " ," , 1)

newString = Mid(aString, 1, hold) + Mid(aString, hold + 2, InStr(hold + 1,
aString, " ", 1))

I want to trim the middle initial and title and keep only last name, comma
and first name.

Any suggestions as to what I am doing wrong here ? Mid$(aString, hold + 2,
InStr(hold + 1, aString, " ", 1))

There is probably some method of the string object that I need to use
instead of those string functions. Right ?

Thanks,
--
Gordon

[Cor Ligthert]

Gordon,

When it is not about millions of names because it is slower (not terrible
however it is), than I would probably in your case use the split with a
delimited string.

http://msdn.microsoft.com/library/de...SplitTopic.asp

The two first (or probably the first and the thirth) in the array are than
what you need.

I hope this helps,

Cor

[Ken Tucker [MVP]]

Hi,

Dim s as string = "Jones, Thomas R, Dr."

dim strComma() as string = s.split(","c)

dim strSpace() as string = strComma(1).Trim.Split(" "c)

Debug.Writeline(strComma(0) & ", " & strSpace(0))

Ken
---------------------------

"Gordon" wrote:

Hi;

I am trying to extract a substring using a combination of the mid() and
Instr() i.e.

aString = "Jones, Thomas R, Dr."

hold = InStr(1, aString, " ," , 1)

newString = Mid(aString, 1, hold) + Mid(aString, hold + 2, InStr(hold + 1,
aString, " ", 1))

I want to trim the middle initial and title and keep only last name, comma
and first name.

Any suggestions as to what I am doing wrong here ? Mid$(aString, hold + 2,
InStr(hold + 1, aString, " ", 1))

There is probably some method of the string object that I need to use
instead of those string functions. Right ?

Thanks,
--
Gordon

[Cor Ligthert]

Ken,

I had the idea that the result would have to be Thomas Jones.

By the way, what is in the USA the first name.
Ken
Kenneth
Kenneth X

By us it is
Cor = Firstname
Cornelis = official firstname or Christianname (seldom used as firstname or
it should be more foreign names)
Cornelis Johannes Marinus = All firstnames (can be one until whatever
amount)

Cor

[Gordon]

Thanks for your suggestions.



"Gordon" wrote:

Hi;

I am trying to extract a substring using a combination of the mid() and
Instr() i.e.

aString = "Jones, Thomas R, Dr."

hold = InStr(1, aString, " ," , 1)

newString = Mid(aString, 1, hold) + Mid(aString, hold + 2, InStr(hold + 1,
aString, " ", 1))

I want to trim the middle initial and title and keep only last name, comma
and first name.

Any suggestions as to what I am doing wrong here ? Mid$(aString, hold + 2,
InStr(hold + 1, aString, " ", 1))

There is probably some method of the string object that I need to use
instead of those string functions. Right ?

Thanks,
--
Gordon